Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
DPAnderson1
Contributor II
Contributor II

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:

AGAHAI
Disch Dept IDDisch Dept CCDisch Dept Division
172977000977000Emergency Department
183977000977000Emergency Department
183977000977000Emergency Department
2607299400994000Medical/Surgical
2600197700977000Emergency Department
2608385000850000Physical 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.

1 Solution

Accepted Solutions
rwunderlich
Luminary Alumni
Luminary Alumni

You should only use "as fieldname" once. The syntax would be:

if(test, mid(...), mid(...)) as [Disch Dept CC]

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

3 Replies
DPAnderson1
Contributor II
Contributor II
Author

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.

Here is my updated script:

Capture.PNG

rwunderlich
Luminary Alumni
Luminary Alumni

You should only use "as fieldname" once. The syntax would be:

if(test, mid(...), mid(...)) as [Disch Dept CC]

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

DPAnderson1
Contributor II
Contributor II
Author

Thank you, Rob! Correct syntax makes all the difference.

Darren