Search in script in previous loaded data according more conditions.

[Juraj_SK]

Dear experts,

I would like to ask you for support.

By loading data I would like to add column “Previous_Price” according 2 conditions (needs to match Product and time frame of Date).

“Previous_Price” shoud be: Find previous price for Product which is first in time-frame starting 365 days backward.

Expected result is distinguishable by colors.

Obstacle for me is to combine 2 conditions by searching the value in previous loaded data.

Thank you very much in advance.

Juraj

[NW1965]

I haven't used your data in this example because I'm not allowed to download anything from this website at my company, however, in principle the following script can be applied to your problem.  the file I created was a simple excel file that holds Product, Date, Price and it doesn't matter what order the data is held in.

Script is:

/*
Load the original data
*/
Original_Data:
LOAD Product
, Date
, Price
FROM [Z:\TestData3.xlsx]
(ooxml, embedded labels, table is Sheet1);

/*
Load the data from resident table, this allows us to sort the data
by product and date, which is required for the final load
*/
Sorted_Data:
NoConcatenate
LOAD Product
, Date
, Price
RESIDENT Original_Data
ORDER BY Product
, Date;

/*
Drop the first table
*/
Drop Table Original_Data;

/*
L:oad the final table using a conditional Previous function to get the last price
*/
Final_Data:
NoConcatenate
LOAD Product
, Date
, Price
, IF(Previous(Product)=Product AND Date-Previous(Date) <= 365,Previous(Price)) AS Previous_Price
RESIDENT Sorted_Data;

/*
Drop the sorted data table, leaving only the final table in memory
*/
Drop Table Sorted_Data;

This should work for you, not sure how quick it is and how much data you are loading, but should be ok.