Taking an If Statement into the Script

[#1]

Hi

I have a developed an If Statement which has over 10 parts to it. So far I have developed this on Qlikview Desktop with a small amount of data. I expect that when I take this into the much larger file we have on the server, used by others this could have a detrimental impact on performance. Therefore I am trying t to see how I can take this into the script but not had much luck so far having so far having not done much scripting.

The IF statement is under the table Annual Budget Table/Expressions.

Any suggestions on how I could approach this would be welcome, I have attached the file below if anyone can help.

Thank you

[#2]

Hi,

Can you please explain whats the requirement(problem statement) is?

Do you want to move that if...else statement to back-end or what  do you want?

Just a suggestion create a field in your Table 1, by saying Right(CC,2) as Right_Flag.

Thanks,

Bikash

[#3]

I am seeing the If Statement as the problem, 4th column of the table shown below. With this containing about 10 elements, I anticipate when I use this with a larger volume of data, it will have a detrimental impact on performance.

Therefore I am trying to examine the option of putting into the load script, thus creating an extra field at this point as a work around on the impact this would have on the performance. I thought it would be a matter of adding the if statement to the load script by creating a new field (as per the example at the bottom). But this has not worked.

What I am trying to do: As per the table below, by using the last two digits of the CC field, and the sub, it creates the combination the mapping requires to work in the 4th column. In the load script I have created an extra field extracting the last two digits of the CC ('CC2').

So on the example below, if the last two digits of the CC are X2, and the sub is 60500, it maps to E20, if the last two digits of the CC, are X3, it gives a classification of E22.

Below is an example of what I have tried to by putting the if statement (underlined) into the script at the end of table2 in the script (but not included in the file I uploaded):

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table1:

LOAD ST,

CC,

right (CC,2) As CC2,

CC_Narration,

Sub,

Sub_Narration,

Annual_Budget,

Current_month,

YTD_Actual,

YTD_Budget,

Variance,

Commitments

FROM

(biff, embedded labels, table is Sheet1$);

Table2:

LOAD RSDT_Num,

SUBJECTIVE AS Sub,

[00-99],

X1,

X2,

X3,

X4,

X5,

Y1,

Y2,

Y3,

Y4,

F13,

if(CC2= 'X1',X1,if(right (CC,2) = 'X2',X2 ,if(right (CC,2) = 'X3',X3 , if(right (CC,2) = 'X4',X4 , if(right (CC,2) = 'X5',X5 , if(right (CC,2) = 'Y1', Y1 ,if(right (CC,2) = 'Y2',Y2 , if(right (CC,2) = 'Y3',Y3 ,if(right (CC,2) = 'Y4',Y4 ,[00-99]))))))))) As Mapping

FROM

(biff, embedded labels, table is [Query by Subjective AND Dept$]);

Any suggestions on how I can amend this script so the IF Statement works would help greatly.

Thanks

Doug

[SunilChauhan · Accepted solution]

proceed like below

table 1:

load

*

from path.xls

concatenate(table1)

Load *

from path2.xls

Load

*,

if(CC2= 'X1',X1,if(right (CC,2) = 'X2',X2 ,if(right (CC,2) = 'X3',X3 , if(right (CC,2) = 'X4',X4 , if(right (CC,2) = 'X5',X5 , if(right (CC,2) = 'Y1', Y1 ,if(right (CC,2) = 'Y2',Y2 , if(right (CC,2) = 'Y3',Y3 ,if(right (CC,2) = 'Y4',Y4 ,[00-99]))))))))) As Mapping

residnt table1;

drop table table1;

hope this help