topic IF not working in qlik sense load script in App Development

IF not working in qlik sense load script

Uttamjit — Fri, 25 Mar 2022 02:32:10 GMT

Hi,

Hoping, someone could help me please. I have been scratching my head on this for last 3 days.

I have this in my load script:

MonthList:

Load
Evaluate(Transaction_Month) as MonthName
Inline [
Transaction_Month
addmonths('$(start_date_fiscal)',0)
addmonths('$(start_date_fiscal)',1)
addmonths('$(start_date_fiscal)',2)
addmonths('$(start_date_fiscal)',3)
addmonths('$(start_date_fiscal)',4)

](delimiter is ';');

Fiscal1:
Load num(1) as ID, MonthName, quartername(MonthName,0,1) as Quarter1 resident MonthList;
Concatenate
Load num(4) as ID, MonthName, quartername(MonthName,0,4) as Quarter4 resident MonthList;

NoConcatenate
FinalFiscal:

Load
ID,MonthName,Quarter1,Quarter4, Coalesce(Quarter1,Quarter4) as FinalQuarter

resident Fiscal1;
drop table Fiscal1;

The output of this in table is:

Somehow, I can't able to get the correct value in FinalQuarter for ID=4.

I have also tried the IF expression in load script instead of coalesce like, but it still doesn't work

ID,MonthName,Quarter1,Quarter4, if(ID=4, Quarter4,(IF(ID=1, Quarter1))) ) as FinalQuarter

Re: IF not working in qlik sense load script

vinieme12 — Tue, 19 Apr 2022 03:38:04 GMT

Fiscal1:
Load num(1) as ID, MonthName, quartername(MonthName,0,1) as Quarter1 resident MonthList;
Concatenate
Load num(4) as ID, MonthName, quartername(MonthName,0,4) as Quarter4 resident MonthList;

Your Fiscal table will be as below

ID | MonthName | Quarter1 | Quarter 4

1 | 1/1/2019 | Jan-Mar 19 | null

1 | 1/4/2019 | Apr-Jun 19 | null

4 | 1/1/2019 | null | Jan-Mar 18

4 | 1/4/2019 | null | Apr-Jun 19

What is the point in doing coalesce in the above table when either of the two columns will always be null??

for num(1) quarter4 will always be null

for num(4) quarter1 will always be null

you can just do

Fiscal1:
Load num(1) as ID, MonthName, quartername(MonthName,0,1) as Quarter1

,quartername(MonthName,0,1) as FinalQuarter

resident MonthList;
Concatenate
Load num(4) as ID, MonthName, quartername(MonthName,0,4) as Quarter4

,quartername(MonthName,0,4) as FinalQuarter

resident MonthList;

Re: IF not working in qlik sense load script

Uttamjit — Mon, 25 Apr 2022 21:56:58 GMT

Hi Vineeth,

I originally tried your way only, but it doesn't work. Why I was doing different column names for each load was just another way to get the original problem solved, although couldn't solve it this way too. The problem still stays the same. Somehow, the first 3 statement loads with ID=1,2 and 3 work, but after that the year in the quarter is always incorrect for ID >=4. The statement works if I wrap the quartername formula with text function, but I don't want to do that given then my column doesn't remain a date.

text(quartername(MonthName,0,4))

Re: IF not working in qlik sense load script

Uttamjit — Mon, 25 Apr 2022 22:12:23 GMT

One more thing, it is not abut the IF function, I guess somehow the quartername function itself is not working for me in the script. Maybe, I am not using it correctly. I am sharing the sample qvf, please have a look at the ID>=4, the quarters look incorrect to me.