Help with if then else statement

[Anonymous]

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

[sunny_talwar]

Try changing this part:

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

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

[sunny_talwar]

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 ;

[Anonymous]

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 ?

[sunny_talwar]

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‌

[settu_periasamy · Accepted solution]

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

like

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;

[jagan]

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.

[sunny_talwar]

Awesome Settu‌‌

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

Thanks for pointing it out.

Best,

Sunny

[Anonymous]

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.

[jagan]

Hi Dave,

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

Regards,

Jagan.