Re: Search in script in previous loaded data according more conditions.
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.
/* 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.