Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm getting a little frustrated trying to convert to Qlik Sense. It's more different than expected and I'm having trouble loading scripts while keeping performance a high priority. So I was hoping someone could help me out with loading some logic into the editor rather than slamming it into set analysis and hindering performance.
I've really scaled things down for this question and I hope my explanation is straight forward. Below is an image of a raw data table and the desired results. I've also attach the Excel file.
The overall goal is to return a table with 5 columns by transferring the logic from set analysis and putting it in the data load editor. I also want to add that set analysis return the desired results in a table.
Set Analysis Logic:
Date:
=Date(if(Status = 'Status_1' AND len(Date_Label_1)>0, Date_Label_1,
if(Status = 'Status_2' AND len(Date_Label_2)>0, Date_Label_2,
if(Status = 'Status_3' AND len(Date_Label_3)>0, Date_Label_3,
if(Status = 'Status_4' AND len(Date_Label_4)>0, Date_Label_4,
if(Status = 'Status_5',aggr(max(Date(RangeMax([Date_Label_5.1],[Date_Label_5.2],[Date_Label_5.3]))),Order_ID),))))))
Label:
=if(Status = 'Status_1' AND len(Date_Label_1)>0, 'Date_Label_1',
if(Status = 'Status_2' AND len(Date_Label_2)>0, 'Date_Label_2',
if(Status = 'Status_3' AND len(Date_Label_3)>0, 'Date_Label_3',
if(Status = 'Status_4' AND len(Date_Label_4)>0, 'Date_Label_4',
if(Status = 'Status_5' AND aggr(RangeMax([Date_Label_5.1],[Date_Label_5.2],[Date_Label_5.3]),Order_ID) = [Date_Label_5.1], 'Date_Label_5.1',
if(Status = 'Status_5' AND aggr(RangeMax([Date_Label_5.1],[Date_Label_5.2],[Date_Label_5.3]),Order_ID) = [Date_Label_5.2], 'Date_Label_5.2',
if(Status = 'Status_5' AND aggr(RangeMax([Date_Label_5.1],[Date_Label_5.2],[Date_Label_5.3]),Order_ID) = [Date_Label_5.3], 'Date_Label_5.3',
)))))))
Location:
=if(Status = 'Status_1' AND len(Date_Label_1)>0, 'Location_1',
if(Status = 'Status_2' AND len(Date_Label_2)>0, 'Location_2',
if(Status = 'Status_3' AND len(Date_Label_3)>0, 'Location_3',
if(Status = 'Status_4' AND len(Date_Label_4)>0, 'Location_4',
if(Status = 'Status_5' AND aggr(RangeMax([Date_Label_5.1],[Date_Label_5.2],[Date_Label_5.3]),Order_ID) = [Date_Label_5.1], 'Location_5.1',
if(Status = 'Status_5' AND aggr(RangeMax([Date_Label_5.1],[Date_Label_5.2],[Date_Label_5.3]),Order_ID) = [Date_Label_5.2], 'Location_5.2',
if(Status = 'Status_5' AND aggr(RangeMax([Date_Label_5.1],[Date_Label_5.2],[Date_Label_5.3]),Order_ID) = [Date_Label_5.3], 'Location_5.3',
)))))))
Here is what I have in the Load Script but I'm getting some errors about data not being loaded...
Analytics:
Load
RangeMax([Date_Label_5.1],[Date_Label_5.2],[Date_Label_5.3) as Max_Date_5,
if([Status] = 'Status_1' AND len([Date_Label_1])>0, [Date_Label_1],
if([Status] = 'Status_2' AND len([Date_Label_2])>0, [Date_Label_2],
if([Status] = 'Status_3' AND len([Date_Label_3])>0, [Date_Label_3],
if([Status] = 'Status_4' AND len([Date_Label_4])>0, [Date_Label_4],
if([Status] = 'Status_5' AND Max_Date_5 = [Date_Label_5.1], [Date_Label_5.1],
if([Status] = 'Status_5' AND Max_Date_5 = [Date_Label_5.2], [Date_Label_5.2],
if([Status] = 'Status_5' AND Max_Date_5 = [Date_Label_5.3], [Date_Label_5.3],'NA')))))))
as BestDate,
if(BestDate = [Date_Label_1], 'Date_Label_1',
if(BestDate = [Date_Label_2], 'Date_Label_2',
if(BestDate = [Date_Label_3], 'Date_Label_3',
if(BestDate = [Date_Label_4], 'Date_Label_4',
if(BestDate = [Date_Label_5.1], 'Date_Label_5.1',
if(BestDate = [Date_Label_5.2], 'Date_Label_5.2',
if(BestDate = [Date_Label_5.3], 'Date_Label_5.3','NA')))))))
as Label,
if(BestDate = [Date_Label_1], 'Location_1',
if(BestDate = [Date_Label_2], 'Location_2',
if(BestDate = [Date_Label_1], 'Location_3',
if(BestDate = [Date_Label_1], 'Location_4',
if(BestDate = [Date_Label_5.1], 'Location_5.1',
if(BestDate = [Date_Label_5.2], 'Location_5.2',
if(BestDate = [Date_Label_5.3], 'Location_5.3','NA')))))))
as Location
Resident Sheet1
group by Order_ID;
Message was edited by: Robert Leggett
Message was edited by: Robert Leggett
Just use The Crosstable Load to transform your data into a "normal" table and on this table you could perform further measures like an inner join on itself with the max. Date which seems to be your "correct" date.
- Marcus