topic Re: Get all possible periods, repeating values. in QlikView

Get all possible periods, repeating values.

Fri, 19 May 2017 12:22:36 GMT

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.

Re: Get all periods between to dates, repeating values.

Fri, 19 May 2017 12:36:45 GMT

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

Re: Get all possible periods, repeating values.

antoniotiman — Fri, 19 May 2017 15:27:52 GMT

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

Re: Get all possible periods, repeating values.

Thu, 25 May 2017 12:27:55 GMT

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