Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Perhaps this blog post helps: How to populate a sparsely populated field
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?
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;
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;
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
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
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