Max in IF statement in load

[greend21]

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

[olivierrobin]

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

[MK_QSL]

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

[greend21]

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;

[shiveshsingh]

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

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

[MK_QSL · Accepted solution]

Temp:

Load Date(CEMinDt-5) as MaxDate;

SQL SELECT Max(ReportDate) as CEMinDt

FROM CSSC_Sequence_SIT.dbo.rptCE_ORMaster;

[olivierrobin]

check the format of the dates between tour database and Qlik

[greend21]

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.

[shiveshsingh]

Hi

Put like this

SQL SELECT Date(Max(ReportDate)-5)

[olivierrobin]

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