Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
merrillbrink
Contributor II
Contributor II

Issue with using Max() or FirstSortedValue() to define field

I have two tables in my data set, one which has all current data and one the historical values of the same fields, each time an update was made.

In the first table I have four fields: Item_Master_ID, Item_Update_ID, Item_Colour and Item_Key.

The historical table contains Item_HistUpdate_ID, Item_HistColour and Item_Key.

Both tables also have a date field which records when the entry update was made.

Basically, the colour of the item can change with an update (but does not always). When an update occurs, the existing Item_Update_ID goes into the historical table becomes the latest Item_HistUpdate_ID. Likewise the corresponding Item_Colour becomes the latest Item_HistColour.

Each Item can have multiple updates recorded as entries in the historical table, all with a unique identifier Item_HistUpdate_ID.

The tables are linked on Item_Key, which is unique for every Item_Master_ID and the data sources appear as follows:

CurrentTable:

Item_KeyItem_Master_IDItem_Update_IDItem_ColourDate
89786542456341891Green04-Apr-2015
73621128613398673Blue16-Mar-2015

378854

18762328002Red02-Apr-2015

    

HistoricalTable:

Item_KeyItem_HistUpdate_IDItem_HistColourDate
897865332567Green31-Mar-2015
897865317832Orange18-Mar-2015
897865309987Orange15-Mar-2015
736211386512Green26-Feb-2015
736211367531Yellow09-Jan-2015
378854311654Blue25-Mar-2015
378854310892Blue21-Mar-2015
378854298761Black03-Mar-2015
378854257439Black21-Feb-2015

I am trying to load a field in the script to display the previous colour of an item (i.e. the latest historical one). By taking the max (Item_HistUpdate_ID) it should provide this, but I have been getting an error when trying to define this in the load script of the Historical table.

I have also tried to load the following the to create this field, but I believe this FirstSortedValue() function can’t be used like this in the load script:

FirstSortedValue(Item_HistColour,- Item_HistUpdate_ID) AS Last_HistFlag

Are there any suggestions on the best way to do this?

I also want to identify any entries where an item colour changed to the current one. E.g. For item_Master_ID 42456, it changed two iterations previous to the current one. Is there a lookup function which can be used to do this in the load script?

Sorry for the long question but any help would be greatly appreciated.

Thanks in advance.

1 Solution

Accepted Solutions
borisman
Creator III
Creator III

see my example - the idea is to concatenate current and histoircal data first in one table, assigning MAster ID at the same time.

Then sort in a script data by Item_id, date descending and use peek function to assign previous color - that's why we need to sort data first.

View solution in original post

5 Replies
borisman
Creator III
Creator III

Hey Colin,


FirstSortedValue is an aggregation function so you have to use with GROUP BY in your script. Could this be an issue?

merrillbrink
Contributor II
Contributor II
Author

Hi Boris,

That's a good point. Would this have to be done in a separate table within the load script and how best should this be structured?

Thanks

borisman
Creator III
Creator III

see my example - the idea is to concatenate current and histoircal data first in one table, assigning MAster ID at the same time.

Then sort in a script data by Item_id, date descending and use peek function to assign previous color - that's why we need to sort data first.

merrillbrink
Contributor II
Contributor II
Author

Hi Boris,

Sorry the delay in getting back to you. Thank you for the proposed solution.

The data structure has changed since then, but I should be able to apply this.

Thanks again.

borisman
Creator III
Creator III

great, I am glad you found it helpful, good luck with your project!