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:
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.