Flagging based on Null Values/Max Date

[#1]

Hello Qlikview Community:

I am trying to create a flag that marks items as distinct in our plan-o-gram. I need to add logic below that first looks for a null value in the inactive_date field, and if there is no null value, then it should look for the max date in that field.

Here is what I have so far:

POGTemp:

Load Distinct

     Store_Number,

     Sku_Number,

     Customer_Number,

     Max(inactive_date)           as Max_Inactive_Date

     '1'                                   as Distinct_POG

Resident CPMPOG

Group By

     Store Number,

     Sku_Number,

     Customer_Number;

Any help would be greatly appreciated.

Regards,

Ben D.

[#2]

In other words, it should flag the row where inactive_date is null, and otherwise (if inactive_date is not null for that store/sku/customer) flag the row with the max inactive_date.

[Gysbert_Wassenaar · Accepted solution]

Maybe like this:

POGTemp:

Load Distinct

     Store_Number,

     Sku_Number,

     Customer_Number,    

           Customer_Number as CNum,

     '1'  as Distinct_POG

Resident CPMPOG

Where len(trim(inactive_date))=0;

concatenate

Load Distinct

     Store_Number,

     Sku_Number,

     Customer_Number,

     Max(inactive_date) as Max_Inactive_Date

     '1'  as Distinct_POG

Resident CPMPOG

Where len(trim(inactive_date))>0

          and not exists(CNum, Customer_Number)

Group By

     Store Number,

     Sku_Number,

     Customer_Number;

Drop Field CNum;

[#4]

In the script after you loaded and grouped the value you could do this:

Load Distinct

     Store_Number,

     Sku_Number,

     Customer_Number,

      Max_Inactive_Date

      Distinct_POG,

     if(isnull(Max_Inactive_Date),'1','0') as Flag

Resident POGTemp;

[#5]

Great, thank you! This was what I was looking for.