Hi everyone! Newbie question here.
I am in the process of transitioning from an Excel based visualization (that eats a TON of memory for everything I am showing) to Qlik. For the Excel version, I have to do a lot of work on the source files before plopping them into the spreadsheet and analyzing them. My hope is to just use the source files with appropriate lookup / joined tables for things I need to do vLookups on.
One specific thing I need to do is evaluate a column during the initial data load and based on the number in the column return a truncated number as a new field. I was able to do this in Excel, but am having a difficult time figuring it out in Qlik.
Below is a sample of the data (from Excel) and the corresponding output:
| AG | AH | AI |
| Disch Dept ID | Disch Dept CC | Disch Dept Division |
| 172977000 | 977000 | Emergency Department |
| 183977000 | 977000 | Emergency Department |
| 183977000 | 977000 | Emergency Department |
| 2607299400 | 994000 | Medical/Surgical |
| 2600197700 | 977000 | Emergency Department |
| 2608385000 | 850000 | Physical Therapy |
Notice the Department ID (AG) has a different prefix based on a new naming convention. The CC (Cost Center - AH) under the "old" naming convention is the last 6 digits of the department ID. Under the "new" naming convention it is the last 5 digits, but it is missing a trailing zero, so I have to add one. I use the Disch Dept CC value to lookup the Disch Dept Division in another table (vLookup in Excel). Here is the formula I used in Excel to get the value in AH:
=IF(AG35<2600000000,NUMBERVALUE(RIGHT(AG35,6)),NUMBERVALUE(CONCATENATE(RIGHT(AG35,5),"0")))
So, what I would like to do is evaluate AG (Disch Dept) during load and return the last 5 or 6 digits (as appropriate) into a new field (Disch Dept CC) and add a zero when necessary. I suspect it will involve a similar If...then statement, but I am unsure of the corresponding "guts" of the operation in Qlik (numbervalue, right, etc...).
Any advice would be welcomed.
