Posts

Showing posts from 2014

Qlikview tips - how to escape characters and space LOAD INLINE table

When you load a table using LOAD * INLINE, and need a space at the end of a string. Then you will find it is impossible to have a space like below in the red highlight part: LOAD * INLINE [ FieldName A   A-B-C B ]; And you can get it by using replace() function like below: LOAD Replace(FieldName,'$',' ' )as FieldName  INLINE [ FieldName A $ A-B-C B ]; This replace() function can also be used to escape "]" or other characters. Really handy and smart design.

Qlikview tips - QMC Document CALs setting

There are different ways in Qlikview Management Console (QMC) to set the .qvw remote access. Here we are talking about remote access using "Document CALs". First you need to create local windows users in the local server where QMC is installed. In Windows Server 2008 RC: Start->Administrative tool ->Server Manager ->Configuration ->Local Users and Groups ->Users In the window with the user list, right click->New User. Here you need to key in at least "User name" and "Full name" and password for the user. The "Full name" will be recognized in the QMC. Note: remember the password you just set, otherwise you need to reset it, as you can not see the password after you create a user. Second, open QMC->Documents->expand the document ->select the .qvw->Document CALs tab ->Assigned Users->add user (Click an icon with two persons at the right below ) ->in the search, key in the "Full name" ...

Qlikview tips - Sort table by expression columns

Qilkview is designed to use conditional sort (use expressions) on dimension column (DC)level, but not on Expression Column (we use EC for short in the rest part of the article) level. EC we talking about here are the columns that created in the Expression tab. Therefore, it took me some time to figure it out how to use it. Normally, you can sort the table in the Sort tab by giving an fixed order of all the columns, including dimension columns and  expression columns. What if your clients want to select specific data based on the EC and sort the result in another column instead of the default fixed sort priority? Then you will find what we discuss here helpful. If you have tried to use expression to sort on the EC, you may already found out the Expression option is gray and can not be selected/edited for them in the  Sort  tab . At the beginning I thought I can not use it. But later I figure it out, you can process this in the Expression option in the DC part. But you...

Use VBA Macro in Nprinting

Nprinting can use VBA macro in the excel report like normal excel report. Only thing that you need to pay attention is the format of the template and the output file. If the report template you are using is not .xlsm format, you can save it as .xlsm format. Or if you add macro in a non-xlsm format file, when you save it, you will got notification and then can save the file to .xlsm format. In order to change the output file format in nprinting, you can open the task>reports , then there is a list of the reports that used in the task.  There is a column called "Output Format" where identify the format of the output of the report, then you can modify it to .xlsm.

VBA excel macro - hide rows with 0 value between 2 rows with specific value

The trick is to use & to get the Range. Sub HideRows() ' ' HideRows Macro '     Dim FoundCellS As Excel.Range     Dim FoundCellE As Excel.Range     Dim iRowS As Integer     Dim iRowE As Integer               Set FoundCellS = Range("sheetname!A:A").Find(what:="string1", lookat:=xlWhole)     Set FoundCellE = Range("sheetname!A:A").Find(what:="string2", lookat:=xlWhole)     iRowS = FoundCellS.Row     iRowE = FoundCellE.Row               For Each c In Worksheets("DailyReport").Range( "B" & iRowS & ":B" & iRowE )         If c.Value < 10 And c.Value <> "" Then Rows(c.Row).EntireRow.Hidden = True     Next      End Sub

Qlikview tips - hide specific rows in a table

There are totally 3 ways to hide specific rows based on dimensions columns and expression column, which are created in "Expression" tab) in Qlikview. If you need to hide the rows based on the dimensions. 1.  You can use "Enable Conditional" in the Dimensions Tab, and then use expressions to select dimensions. The tricky part is to hide the rows based on Expression columns, as Qlikview are not designed to do such job. BUT , we can do it by using expressions. Expression is such a beautiful  thing in Qlikview. 2. Use the "Dimension Limit" tab, where can show top N / N% of a table based on the first expression column. Then we see the limitation, this is only available for the first expression column. 3. Use the expression in one of the expression column and variables. We use  expression_column1 as an example here. The expressions for all the columns are originally like below: For example, expression_column1 =  expression1 , and expression_column2...

Remmina connection problem

Today, I suddenly got the error when using Remmina Remote Desktop Client in ubuntu 12.04. The error is like below:  Unable to connect to RDP server hostname After a search, all the solutions as below dose not work for me: 1. Change connection profile: Advanced- Security from "Negotiate" to "RDP" or "TLS" After doing this, I got a different error, said the "username or password is not correct". And after I tried to re-type-in the password several times, it still did not work. And keep get this error. 2. Delete the host config file: rm ~/.freerdp/known_hosts   Nothing change after I delete the file Finally, recreate the connection worked for me.

Remmina RDP client in ubuntu 12.04

In ubuntu 12.04, there is a nice RDP client, and you do not need to install other third-party soluiton. You can find a nice and simple guide here. http://www.ubuntututorials.com/connect-windows-7-remote-desktop-ubuntu-12-04/ And for some reason, the opened window from Remmina RDP always switches the workspace automatically, that is really inconvenient. And the compizConfig may help: http://askubuntu.com/questions/348913/how-can-i-prevent-programs-from-switching-workspaces It is depressing that, even if you use the compizConfig to lock the workspace for the application, it can still switch unwilled. What a frustrating tricky thing. Finally, I find the reason, and you must click the button "Toggle fullscreen mode" to change the mode of the RDP.

Qlikview usage tips- total mode & expression condition

If you need to sum under a condition and also show the total in the table, two solutions: 1. In the Expression tab: Definition: SUM(IF ..., 0, result ) Total Mode: Select "Expression Total" 2. In the Expression tab: Definition: IF ..., 0, SUM(result) Total Mode: Select "Sum" of Rows

Displaying csv file with "leading-0" in Ubuntu LibreOffice

Image
1. Make the special column as "text" when opening it in LibreOffice When you use LibreOffice to open the csv file with columns with leading-0 as in column "BoekstukNr" as below. You will have a setting page when you open the csv file. Except the general settings for all the columns (number will be recognized as number without leading-0, strings will be recognized as strings) in "Separator options" and "Other options", it is important that you still need to set more in Fields. Otherwise LibreOffice will not know that you want to display a column in a specific type. Especially for a date type, it is recognized as number in LibreOffice and MS excel. In the Fields part, you first select the column with leading-0, then change the "Column type " in place 2 to "Text", then after you open it, the leading-0 will be kept. Note: if you do not set it here and opened the csv file, then the leading-0s are already gone. Even you set in a...

php 5.3, 5.4, 5.5 upgrade/downgrade in ubuntu 12.04

The start is from php 5.3.10, a ubuntu 12.04 default version.   Upgrade to 5.4.x  sudo apt-get install python-software-properties sudo add-apt-repository ppa:ondrej/ php5-oldstable sudo apt-get update sudo apt-get install php5 Upgrade to 5.5.x  sudo apt-get install python-software-properties sudo add-apt-repository ppa:ondrej/ php5 sudo apt-get update sudo apt-get install php5 Downgrade to 5.4.x from 5.5.x sudo apt-get install -y ppa-purge sudo add-apt-repository -y ppa:ondrej/php5-oldstable sudo apt-get update sudo ppa-purge ppa:ondrej/php5 sudo apt-get update sudo apt-get upgrade -y  sudo apt-get autoremove -y  sudo apt-get autoclean Downgrade to 5.3.x from 5.5.x sudo apt-get install -y ppa-purge sudo ppa-purge ppa:ondrej/php5 sudo apt-get update sudo apt-get upgrade -y  sudo apt-get autoremove -y  sudo apt-get autoclean Downgrade to 5.3.x from 5.4.x sudo apt-get install -y ppa-purge sudo ppa-purge pp...

sudo without password to write to file that need root right

It is necessary that sometimes you need to write the data  to the root files with "sudo" and "password". But if you need to set auto-run scripts, then it is a problem to enter the password. Here is the way how you do it in Linux system. Change the owner of the file defaultly with root right to the username and group who you are used to write the data: sudo chown -R username:group /path/to/root/directory That is all,  pretty simple. Then you can set a cronjob to write and modify the directory now. 

LInux system structure

reference:  http://slashmedia.wordpress.com/2007/12/23/linux-directory-structure/ The directory structure of Linux/other Unix-like systems is very intimidating for the new user, especially if he/she is migrating from Windows. In Windows, almost all programs install their files (all files) in the directory named: `Program Files.’ Such is not the case in Linux. The directory system categorises all installed files. All configuration files are in /etc, all binary files are in /bin or /usr/bin or /usr/local/bin. Here is the entire directory structure along with what they contain: /  – Root directory that forms the base of the file system. All files and directories are logically contained inside the root directory regardless of their physical locations. /bin  – Contains the executable programs that are part of the Linux operating system. Many Linux commands, such as cat, cp, ls, more, and tar, are locate in /bin /boot  – Contains the Linux kernel and other files...

bash string match on specific lines using grep

Check the string contains "error" in the last line of a file: tail -1 error.log | grep -E "Error" && echo "yes" or grep Error error.log  |  tail -1 or [[ $(tail -1 error.log | grep -E "Error") ]] && echo "yes" note: the "[[" and "[" usage can be really different, reference here or tail -1 error.log | grep - q E "Error" && echo yes  note: -q is used to silence the output of grep. The first 2 cmds will at the same time return the "error" lines in the file, and the last 2 cmds return only  "yes" as the result.

mysql tips - export csv file from MYSQL database in remote server

1.  Export csv file in client server directly This is the situation that I think most beginners will have for doing development or backup of the mysql database. You have a remote mysql server to hold the data, and a local client server to develope. As solution 2 provided by MYSQL official csv export solution will export the file only on the database server (here is on remote server), that is not what we want here. mysql -h hostname -u user -p'password' -P port dbname -e "SELECT * FROM tablename WHERE Date<='2013-07-14';" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >  tablename .csv Using variables and in shell script (which easily cause problems): mysql -h $hostname -u $user -p $pass -P $port $dbname -e "SELECT * FROM $tablename WHERE Date<= '$oldDate'; " | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >  tablename .csv For the sed...

xml validation and invalid characters solution in PHP/Python

Validate xml file, the website below can do the trick and show you invalid xml characters: http://www.xmlvalidation.com/ When you use xml parser in PHP like simplexml_load_string()  of version 1.0 to process the xml file based on version 1.1, then it is possible that you will have this issue. You may got an error from your script like below: simplexml_load_string(): Entity: line 166858: parser error : xmlParseCharRef: invalid xmlChar value 0 or  simplexml_load_string(): Entity: line 166858: parser error : xmlParseCharRef: invalid xmlChar value 14 This is because in the version 1.0, the characters like &#x0, &#xE is not allowed. The rule is that all characters composition /&#x[0-1]?[0-9A-E]/  is not allowed. So we can replace the xml file like below to fix this problem: $string = file_get_content($xmllink); preg_replace('/&#x[0-1]?[0-9A-E]/', ' ', $string); The xml validation is similar in python, to process a big .xml file, we do not ready it on...