Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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;
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;
Great, thank you! This was what I was looking for.