Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Flagging based on Null Values/Max Date

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Not applicable
Author

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

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;


talk is cheap, supply exceeds demand

View solution in original post

Not applicable
Author

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;

Not applicable
Author

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