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

[Anonymous]

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_Key	Item_Master_ID	Item_Update_ID	Item_Colour	Date
897865	42456	341891	Green	04-Apr-2015
736211	28613	398673	Blue	16-Mar-2015
378854	18762	328002	Red	02-Apr-2015

HistoricalTable:

Item_Key	Item_HistUpdate_ID	Item_HistColour	Date
897865	332567	Green	31-Mar-2015
897865	317832	Orange	18-Mar-2015
897865	309987	Orange	15-Mar-2015
736211	386512	Green	26-Feb-2015
736211	367531	Yellow	09-Jan-2015
378854	311654	Blue	25-Mar-2015
378854	310892	Blue	21-Mar-2015
378854	298761	Black	03-Mar-2015
378854	257439	Black	21-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.

[Anonymous]

Hey Colin,

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

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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.

[Anonymous]

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