topic Re: Max in IF statement in load in QlikView

Max in IF statement in load

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

I am trying to put the below calculation into my load so I can then use this flag in set analysis. Can this not be done? I keep receiving errors. I didn't think I would need a group by since Max is within the IF.

If(ReportDate>=Max(ReportDate)-5,1,0) as CADateFlag

Re: Max in IF statement in load

olivierrobin — Thu, 22 Mar 2018 15:53:37 GMT

hello,

in a load you're working at row level

you should calculate the max valeur first in one load

and use the value in the 2nd one

Re: Max in IF statement in load

MK_QSL — Thu, 22 Mar 2018 15:53:55 GMT

You have to do something like below

Temp:
Load Date(Max(ReportDate)-5) as MaxDate Resident YourTableName;

Let vMaxDate = Peek('MaxDate');

Drop Table Temp;

Now you can use

If(ReportDate>='$(vMaxDate)',1,0) as CADateFlag

Re: Max in IF statement in load

greend21 — Thu, 22 Mar 2018 16:36:23 GMT

It is assigning 1 to all report dates. Any idea why? I followed your instructions and it seems like it should work.

Temp:
SQL SELECT Max(ReportDate)-5 CEMinDt
FROM CSSC_Sequence_SIT.dbo.rptCE_ORMaster;
LET vCEMinDt = Peek('CEMinDt');
Drop Table Temp;

Load *,
IF(ReportDate>='$(vCEMinDt)',1,0) as CEDateFlag
;
Table1:
SQL SELECT ReportDate,
BackLog CEBacklog,
MultiNational CEMultinational,
CreditDebit CECreditDebit,
CE_EfftAge_Days,
CE_BillAge_Days,
CE_DueAge_Days,
CE_YTDTotalAmt,
OR_90PlusDayYTDAmt,
OR_EfftAge_0_30Amt,
OR_EfftAge_31_60Amt,
OR_EfftAge_61_90Amt,
OR_EfftAge_91Amt,
OR_EfftAge_0_30Cnt,
OR_EfftAge_31_60Cnt,
OR_EfftAge_61_90Cnt,
OR_EfftAge_91Cnt
FROM CSSC_Sequence_SIT.dbo.rptCE_ORMaster;

Re: Max in IF statement in load

shiveshsingh — Thu, 22 Mar 2018 16:37:11 GMT

Max(ReportDate) --> This will give you a integer.

Better to store value in some variable as suggested by Manish and then use it further.

Re: Max in IF statement in load

MK_QSL — Thu, 22 Mar 2018 16:39:14 GMT

Temp:

Load Date(CEMinDt-5) as MaxDate;

SQL SELECT Max(ReportDate) as CEMinDt

FROM CSSC_Sequence_SIT.dbo.rptCE_ORMaster;

Re: Max in IF statement in load

olivierrobin — Thu, 22 Mar 2018 16:39:35 GMT

check the format of the dates between tour database and Qlik

Re: Max in IF statement in load

greend21 — Thu, 22 Mar 2018 16:40:18 GMT

I checked the temp table before I dropped it and it doesn't return an integer, it returns the date. It returns the correct date. I did store it in a variable as suggested.

Re: Max in IF statement in load

shiveshsingh — Thu, 22 Mar 2018 16:41:27 GMT

Put like this

SQL SELECT Date(Max(ReportDate)-5)

Re: Max in IF statement in load

olivierrobin — Thu, 22 Mar 2018 16:44:04 GMT

be careful that '$(vMaxDate)' returns a string and not a value in a date format

you should have to use date functions in QV or SQL to have two values of same type

Re: Max in IF statement in load

greend21 — Thu, 22 Mar 2018 16:46:37 GMT

In SQL it has to be formated like SQL SELECT CAST(Max(ReportDate)-5 as Date) but whether I do this or not I get the result in a date format.

Re: Max in IF statement in load

greend21 — Thu, 22 Mar 2018 16:46:58 GMT

I am getting a date just like in the database.

Re: Max in IF statement in load

olivierrobin — Thu, 22 Mar 2018 16:49:04 GMT

date and time

can't this be the problem ?