Having issues with resident loads, preceding loads, defining fields, Qlik functions and the load order in scripts

[Anonymous]

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

[marcus_sommer]

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