Skript auto fill up missing lines

[robbybalboa]

Dear all,

I have to create a table with the price for each day.
The basic table has stored only the price by item by date.

Following data existing:       

Item	Date	Price
1	01.01.2015	3
1	01.02.2015	2.5
1	01.03.2015	2.7
2	01.01.2015	2.7
2	01.02.2015	2.2
2	01.03.2015	2.4
3	01.01.2015	2.4
3	01.02.2015	1.9
3	01.03.2015	2.1

Result looks like

Item	Date	Price
1	01.01.2015	3
1	02.01.2015	3
1	03.01.2015	3
1	04.01.2015	3
1	05.01.2015	3
1	….	3
1	30.01.2015	3
1	01.02.2015	2.5
1	02.02.2015	2.5
1	02.03.2015	2.5

Any ideas to have a well performing soloution?

[Gysbert_Wassenaar]

Perhaps this blog post helps: How to populate a sparsely populated field

[robbybalboa]

Hi Gysbert,
Thank you. The solution works fine with one dimension (Date)

But how to modify the script if there are two (date, item) or ore dimension?

[Gysbert_Wassenaar]

Then you have to make sure to order the table by the other dimensions first and finally the date and when you check for nulls in the price field you also need to check in the other dimensions differ. Something like:

LOAD

     Item,

     Date,

     If(previous(Item)=Item and len(trim(Price))=0, peek(Price), Price) as Price

Resident TempTable

Order by Item, Date;

[sunny_talwar]

UPDATED: Script and application both changed....

Try this script:

Table:

LOAD Item,

     Date#(Date, 'DD.MM.YYYY') as Date,

     Price

FROM

[https://community.qlik.com/thread/165747]

(html, codepage is 1252, embedded labels, table is @1);

FOR i = 1 to 3

  MinMaxDate:

  LOAD Min(Date) as MinDate,

  Max(Date) as MaxDate

  Resident Table

  Where Item = $(i);

  Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

  Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

  TempTable:

  LOAD Date(RecNo() + $(vMinDate), 'DD.MM.YYYY') as Date,

  $(i) as Item

  Autogenerate vMaxDate - vMinDate;

NEXT

Join(Table)

LOAD *

Resident TempTable;

FinalTable:

NoConcatenate

LOAD Item,

  Date,

  If(IsNull(Price), Peek('Price'), Price) as Price

Resident Table

Order By Item, Date;

DROP Tables TempTable, Table, MinMaxDate;

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

tabInput:
LOAD * FROM [https://community.qlik.com/thread/165747] (html, codepage is 1252, embedded labels, table is @1);

NoConcatenate
tabOutput:
LOAD Item, 
     Date(Date+IterNo()-1) as Date,
     Price
While IterNo()=1 or Item=Previous(Item) and Date+IterNo()-1<PreviousDate;     
LOAD *, Previous(Date) as PreviousDate
Resident tabInput
Order By Item, Date desc;

DROP Table tabInput;

hope this helps

regards

Marco

[robbybalboa]

Dear Gysbert, dear sunindia and Marco,
Thank you all for your work.
Think each of your soloution will work.
The fastest and shortest skript will be this of Marco.

With regards

Robert

[sunny_talwar]

As long as you have an answer, we don't care who gave it. I think the idea of the community is to be able to get multiple solutions for a single query and then leaving it to the person who asked to choose what might he think would be the best solution for his current scenario.

So we are glad you found your solution.

Best,

Sunny