Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Flagging based on Null Values/Max Date

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
4 Replies
Not applicable

Re: Flagging based on Null Values/Max Date

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.

Re: Flagging based on Null Values/Max Date

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
Not applicable

Re: Flagging based on Null Values/Max Date

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

Re: Flagging based on Null Values/Max Date

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

Community Browser