Product Status Change Timeline Data to Load and Calendar

[roberto99]

Hi Experts,

Here I have attached the excel with sample data.

Left hand side table is my input and right hand side is the expected output.

Please guide me through the best approach.

Thanks in advance.

[Quy_Nguyen]

Hi,

From my understanding, you're trying to split the effective day of your article from that source table. As your output, I believe the Fec_Fin should be 2/20/2021 and 2/19/2021. So please try this below code:

Raw:
Load Art, Cod, Price, Date(Fec_Ini) as Fec_Ini, Date(Fec_Fin) as Fec_Fin;
LOAD * INLINE [
Art, Cod, Price, Fec_Ini, Fec_Fin
45238,	K,	7,	2/16/2021,	2/20/2021
45238,	O,	9,	2/12/2021,	2/20/2021
45238,	W,	12,	1/31/2021,	2/20/2021
45238,	Z,	2,	1/15/2021,	2/20/2021
25322,	C,	2,	2/16/2021,	2/19/2021
25322,	E,	6,	2/13/2021,	2/19/2021
];

// Create interval table 
Data:
Load 
	Art, 
    Cod, 
    Price, 
    Fec_Ini, 
    If(Art <> Peek(Art) or isnull(Peek(Art)), 
    	Fec_Fin,
        Peek(Fec_Ini)-1
       ) as Fec_Fin, 
    Fec_Fin as Fec_Fin_Org
Resident Raw
Order by Art, Fec_Ini desc;

Drop Table Raw;


Let vRows = NoOfRows('Data');

Final:
LOAD * INLINE
[
	Art, Cod, Price, Fec
]; 
    
If( vRows > 0 ) Then
	For i = 0 to vRows -1
   		// Get fields value of current row
    	Let vArt = Peek('Art', i,'Data');
        Let vCod = Peek('Cod', i,'Data');
        Let vPrice = Peek('Price', i,'Data');
        Let vStartDate = Num(Date(Peek('Fec_Ini', i,'Data')));
        Let vEndDate = Num(Date(Peek('Fec_Fin', i,'Data')));
        
        // Loop for generating date
        Concatenate (Final)
        Load
        	'$(vArt)' as Art,
            '$(vCod)' as Cod,
            '$(vPrice)' as Price,
            Date($(vStartDate) + IterNo() -1) as Fec
        AutoGenerate 1
        While IterNo() <= $(vEndDate) - $(vStartDate)+1 ;   
    Next;  
EndIf;

Drop Table Data;

I don't know what is the purpose of this result table in your project. If you want to do something like matching articles with date in your Facts table with the Article dimension table (as the source in example) to find the correct Price, I would suggest another approach using Interval match function  (here), it is very strong function to do this kind of work from.

Hope it helps.

[roberto99]

Thank you very much, but it does not generate the result I am looking for, it generates dates from 12/30/1899 to 02/21/2021 and only for Code K and Article 45238.

[Quy_Nguyen]

Please make sure you're using the correct date format.

You can take a look at this attached qvf. It generates exact what you want.

[roberto99]

Yes, what I am looking for is to relate the price of the item sold on a date, where it coincides in the range of dates with those of a table similar to the one shown, and with a descending code order, for example if item 45222 is sold on Date 02 / 02/2021 to 4 and in this table the Cod X start date 02/01/2021 End date 02/15/2021 price 2 show 2, but if the sale date is 02/04/2021 it is sold at 4 and in this table in Cod V Start Date 02/03/2021 End Date 02/15/2021 price 3 mark 3, because V in descending order is before X and on active date as of 02/03/2021, thank you

[roberto99 · Accepted solution]

Sublime, it was my fault, the format of the dates, I added the storage dimension and it works perfectly, thank you very much