Truncate a Number based on a Condition in Load Statement
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:
Disch Dept ID
Disch Dept CC
Disch Dept Division
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:
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...).
Did some searching and I think I found a way to extract what I need from the original department ID. However, I am getting an error with the If...then...else statement -- Expecting a ')' error. It looks like all my parentheses are closed, however it still is looking for another closed paren. Also the second (else) Mid statement does not appear to be "taking". So it is obvious that I have messed something up with the syntax.