topic Re: Help with if then else statement in QlikView

Help with if then else statement

Anonymous — Thu, 03 Sep 2015 01:01:59 GMT

Hi, I'm trying to write and if then else statement and it runs but seems to ignore the IF statement and just runs the code after "ELSE". The log file shows the variables populating correctly but I still can't get it working. The code executes fine after the ELSE - it extracts the exact data I needs and STORES fine, so it's just the IF/THEN part I'm getting wrong.

Let Today = num(today()-7);

Let Today2 = num(Today()-8);

Let Mon = month($(Today));

Let Mon2 = month($(Today2));

Let YR = year($(Today));

LOAD

[Total FTE Hours],

Data,

Date,

MONTH(Date) as Mon3,

Year(Date) as Year3

IF $(Mon) = $(Mon2) then

Test2:

NoConcatenate

Load *

Resident Test

where

(Mon3 = '$(Mon)' and Year3 ='$(YR)');

STORE Test2 into D:\Users\d370858\Desktop\QV Testing\AOSourceData.$(Mon).qvd;

Drop table Test2;

Drop table Test;

ELSE // This is the part that always runs no matter what the IF is

Test2:

NoConcatenate

Load *

Resident Test

where

(Mon3 = '$(Mon)' and Year3 ='$(YR)');

STORE Test2 into D:\Users\d370858\Desktop\QV Testing\AgentOverheadsSourceData.$(Mon).qvd;

Drop table Test2;

Test3:

NoConcatenate

Load *

Resident Test

where

(Mon3 = '$(Mon2)' and Year3 ='$(YR)');

STORE Test3 into D:\Users\d370858\Desktop\QV Testing\AgentOverheadsSourceData.$(Mon2).qvd;

Drop table Test3;

Drop table Test;

END IF ;

Thanks,

Dave

Re: Help with if then else statement

sunny_talwar — Thu, 03 Sep 2015 01:19:55 GMT

Try changing this part:

Let Mon = Num(month($(Today)));

Let Mon2 = Num(month($(Today2)));

Re: Help with if then else statement

sunny_talwar — Thu, 03 Sep 2015 01:24:56 GMT

Other changes might also be required:

Let Today = num(today()-7);

Let Today2 = num(Today()-8);

Let Mon = Num(month($(Today)));

Let Mon2 = Num(month($(Today2)));

Let MonName = month($(Today));

Let MonName2 = month($(Today2));

Let YR = year($(Today));

LOAD

[Total FTE Hours],

Data,

Date,

MONTH(Date) as Mon3,

Year(Date) as Year3

IF $(Mon) = $(Mon2) then

Test2:

NoConcatenate

Load *

Resident Test

where (Mon3 = '$(MonName)' and Year3 ='$(YR)');

STORE Test2 into D:\Users\d370858\Desktop\QV Testing\AOSourceData.$(MonName).qvd;

Drop table Test2;

Drop table Test;

ELSE // This is the part that always runs no matter what the IF is

Test2:

NoConcatenate

Load *

Resident Test

where

(Mon3 = '$(MonName)' and Year3 ='$(YR)');

STORE Test2 into D:\Users\d370858\Desktop\QV Testing\AgentOverheadsSourceData.$(MonName).qvd;

Drop table Test2;

Test3:

NoConcatenate

Load *

Resident Test

where

(Mon3 = '$(MonName2)' and Year3 ='$(YR)');

STORE Test3 into D:\Users\d370858\Desktop\QV Testing\AgentOverheadsSourceData.$(MonName2).qvd;

Drop table Test3;

Drop table Test;

END IF ;

Re: Help with if then else statement

Anonymous — Thu, 03 Sep 2015 01:33:19 GMT

So first of all, many thanks.

This worked ...

Let Mon = Num(month($(Today)));

Let Mon2 = Num(month($(Today2)));

What's confusing is that even without the Num, they are the essentially the same logic.

Looking in the log file before this NUM change showed

IF August = August then

but then the next line was the ELSE statement. So even though the variables are identical, it still counted them as different.

Adding the Num changed this to

IF 8 = 8 then

which worked.

So any idea why QV looks at the text as different but not the number ?

Re: Help with if then else statement

sunny_talwar — Thu, 03 Sep 2015 01:42:10 GMT

To tell you the truth, I have no idea why Month did not work. May be we can ask the experts about it: swuehl‌, rwunderlich‌‌, jagan‌, MRKachhiaIMP‌, Henric‌, MarcoWedel‌

Re: Help with if then else statement

settu_periasamy — Thu, 03 Sep 2015 02:09:14 GMT

This may be the problem with the String.. Try to add the single quote in the variable.

Let Mon = month($(Today));

Let Mon2 = month($(Today2));

...

IF '$(Mon)' = '$(Mon2)' then

...

Edit:

for checking purpose, try this script

if August=August then //This Won't work

Let a=5+5;

ELSEIF 'August'='August' then //This will work

let a=10+10;

ELSE

Let a=10+5;

ENDIF;

if 8=8 then

Let b=5+5;

ELSE

Let b=10+5;

ENDIF;

Re: Help with if then else statement

jagan — Thu, 03 Sep 2015 02:19:02 GMT

Hi Dave,

For string variables you have enclose in single quotes, like below

Looking in the log file before this NUM change showed

IF August = August then

but then the next line was the ELSE statement. So even though the variables are identical, it still counted them as different.

If both August are variables then it should be

IF '$(variable1)' = '$(variable2)' then OR

IF variable1 = variable2 then // You can directly compare variables no need of $()

Hope this helps you.

Regards,

Jagan.

Re: Help with if then else statement

sunny_talwar — Thu, 03 Sep 2015 02:20:00 GMT

Awesome Settu‌‌

I did not realize that and was running crazy over why it wasn't working.

Thanks for pointing it out.

Best,

Sunny

Re: Help with if then else statement

Anonymous — Thu, 03 Sep 2015 02:50:59 GMT

Both ways work now so double kudos here - Thanks to Sunny for using NUM in the variable (still getting my head round the way Qlik handles dates) and thanks to Jagan for the apostrophes.

Re: Help with if then else statement

jagan — Thu, 03 Sep 2015 03:17:02 GMT

Hi Dave,

Please close this thread by giving Correct Answer to the post which helps you in getting the answer.

Regards,

Jagan.