Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Get all possible periods, repeating values.

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.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

3 Replies
Not applicable
Author

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.

Peek() vs Previous() – When to Use Each

antoniotiman
Master III
Master III

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

Not applicable
Author

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