topic Re: Skript auto fill up missing lines in QlikView

Skript auto fill up missing lines

robbybalboa — Wed, 27 May 2015 12:42:06 GMT

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?

Re: Skript auto fill up missing lines

Gysbert_Wassenaar — Wed, 27 May 2015 12:43:55 GMT

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

Re: Skript auto fill up missing lines

robbybalboa — Wed, 27 May 2015 14:41:03 GMT

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?

Re: Skript auto fill up missing lines

Gysbert_Wassenaar — Wed, 27 May 2015 14:49:34 GMT

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;

Re: Skript auto fill up missing lines

sunny_talwar — Wed, 27 May 2015 14:55:53 GMT

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;

Re: Skript auto fill up missing lines

MarcoWedel — Wed, 27 May 2015 23:28:35 GMT

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

Re: Skript auto fill up missing lines

robbybalboa — Thu, 28 May 2015 04:16:45 GMT

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

Re: Skript auto fill up missing lines

sunny_talwar — Thu, 28 May 2015 10:15:01 GMT

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