Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

robbybalboa
New Contributor III

Skript auto fill up missing lines

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:       

ItemDatePrice
101.01.20153
101.02.20152.5
101.03.20152.7
201.01.20152.7
201.02.20152.2
201.03.20152.4
301.01.20152.4
301.02.20151.9
301.03.20152.1

Result looks like

 

ItemDatePrice
101.01.20153
102.01.20153
103.01.20153
104.01.20153
105.01.20153
1….3
130.01.20153
101.02.20152.5
102.02.20152.5
102.03.20152.5

Any ideas to have a well performing soloution?

Tags (3)
1 Solution

Accepted Solutions

Re: Skript auto fill up missing lines

Hi,

one solution could be:

QlikCommunity_Thread_165747_Pic1.JPG

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

7 Replies

Re: Skript auto fill up missing lines

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


talk is cheap, supply exceeds demand
robbybalboa
New Contributor III

Re: Skript auto fill up missing lines

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

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;


talk is cheap, supply exceeds demand

Re: Skript auto fill up missing lines

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

Hi,

one solution could be:

QlikCommunity_Thread_165747_Pic1.JPG

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
New Contributor III

Re: Skript auto fill up missing lines

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

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

Community Browser