topic Re: Flagging based on Null Values/Max Date in QlikView

Flagging based on Null Values/Max Date

Wed, 03 Apr 2013 14:19:02 GMT

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.

Re: Flagging based on Null Values/Max Date

Wed, 03 Apr 2013 15:25:55 GMT

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

Gysbert_Wassenaar — Wed, 03 Apr 2013 16:28:03 GMT

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;

Re: Flagging based on Null Values/Max Date

Wed, 03 Apr 2013 16:35:05 GMT

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;

Re: Flagging based on Null Values/Max Date

Wed, 03 Apr 2013 20:04:45 GMT

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