Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning folks,
I have a situation here that I'm not getting any soluction by my own...
I have 3 main fields to do this:
the product ID "CD_PRODUTO" to identify it
the periods reference "PERIODO" formated like 'YYYYMM' that tell when some product was bought
and the cost "CUSTO" that tell how much each unit cost by that purchase.
The problems is that the most part of the products aren't purchased every month. I need to show every product, period and price, even if it hasn't been purchased by this period, and by reapeating the price of the last bought.
There are a picture of the data table.
Hi,
Temp:
LOAD *,Previous(PERIODO) as P_1,Previous(CUSTO) as C_1 Inline [
CD_PRODUTO,PERIODO,CUSTO
1,201601,15
1,201606,16
1,201609,18
2,201604,25
2,201606,26
2,201610,28
];
Concatenate
LOAD CD_PRODUTO,
P_1 + IterNo() as PERIODO,C_1 as CUSTO
//Date(MonthStart(Date#(P_1,'YYYYMM'),IterNo()-1),'YYYYMM') as PERIODO1 //if You need PERIODO as Date Field
Resident Temp
While (P_1 + IterNo()+1) <= PERIODO;
Regards,
Antonio
Hi Thales
To fill in the missing dates you'll have to create a MasterCalendar on which you should join your sales figures.
You will have Null values for the dates where you did not sell a certain product. You can use the Peek and Previous functions to populate these fields with Null values.
Hi,
Temp:
LOAD *,Previous(PERIODO) as P_1,Previous(CUSTO) as C_1 Inline [
CD_PRODUTO,PERIODO,CUSTO
1,201601,15
1,201606,16
1,201609,18
2,201604,25
2,201606,26
2,201610,28
];
Concatenate
LOAD CD_PRODUTO,
P_1 + IterNo() as PERIODO,C_1 as CUSTO
//Date(MonthStart(Date#(P_1,'YYYYMM'),IterNo()-1),'YYYYMM') as PERIODO1 //if You need PERIODO as Date Field
Resident Temp
While (P_1 + IterNo()+1) <= PERIODO;
Regards,
Antonio
Looks like you saved the day, my friend.
Thank you.
Just for knowledge. I only used two loops following your example. The first one was like yours, to load new lines for each blank period until the next one arrives, and another, to load new lines with the last period of purchase until today, in case of no purchases in the last month.
Really thankful