Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
sarahshong
Contributor III
Contributor III
‎2020-09-19 09:49 PM

Creating a master calendar and filling in missing data

I have QVD data that has missing date (weekends and holidays) and output (prices) for those dates. I'm trying to create a master calendar and fill in the missing date's data using the last price. I'm tried looking through other forums and I can't seem to get it to work. 

 

Prices:
LOAD
DATE,
CURVE_NM,
PRICE,
LABEL

FROM
Prices.QVD

WHERE DATE>'12/30/18' and DATE<'02/01/2019' 
;


TempTable_Rates:

LOAD Min(DATE) as minDate,
Max(DATE) as maxDate

resident Prices;

Let vMinDate = Num(Peek('minDate',0,'TempTable_Rates'));

Let vMaxDate = Num(Peek('maxDate',0,'TempTable_Rates'));

DROP table TempTable_Rates;


Date_Ranges:
LOAD
$(vMinDate) + IterNo()-1 as DateNum,
Date($(vMinDate) + IterNo()-1) as TempDate
AutoGenerate 1 while $(vMinDate) + IterNo()-1 <= $(vMaxDate);


Master_Calendar:
Load

TempDate as DATE,
Month(TempDate) as DATE_Month,
Year(TempDate) as DATE_Year
Resident Date_Ranges Order By TempDate ; /* so that above values can be propagated downwards */

Drop Table Date_Ranges;

join
Load

DATE,
CURVE,
LABEL,
If(isnull(PRICE),peek(PRICE),PRICE) as PRICE

Resident Prices Order by CURVE,LABEL,DATE;

Labels (1)
Labels
2,215 Views
0 Likes
6 Replies
rubenmarin
MVP
MVP
‎2020-09-20 05:01 AM

Hi, the peek should be used in another step after the join.

The join should return in a table with all dates, some with PRICE and other with PRICE as null. After the join you can add another step to fill the data, but it will be better if you keep the Master_Calendar as an independent table to have all date related fields (DATE_Month, DATE_Year).

To do this you can add after "Drop Table Date_Ranges;", removing the join you have after that and replace with:

Outer Join (Prices)
LOAD DATE Resident Master_Calendar;

PricesFull:
LOAD DATE,
  CURVE,
  LABEL,
  If(IsNull(PRICE), Peek(PRICE), PRICE) as PRICE
Resident Prices
Order By DATE;

DROP Table Prices;

 

2,178 Views
sarahshong
Contributor III
Contributor III
‎2020-09-20 11:57 AM
Author

In response to rubenmarin

Thank you! that worked partially, but I'm only receiving the most recent dates data for the first row of the other two fields. 

For any given day, I need to have all the Curve and Label fields from the prior good day and the most recent price for those.

the output in the third table below is what I'm looking for. The left hand table is the base data and the second table is what I'm currently getting. Any suggestions?

 

DATECURVELABELPRICE DATECURVELABELPRICE DATECURVELABELPRICE
12/31/2018Applesfirst1.5 12/31/2018Applesfirst1.5 12/31/2018Applesfirst1.5
12/31/2018Applessecond1.6 12/31/2018Applessecond1.6 12/31/2018Applessecond1.6
12/31/2018Orangesfirst1.7 12/31/2018Orangesfirst1.7 12/31/2018Orangesfirst1.7
12/31/2018Orangessecond1.8 12/31/2018Orangessecond1.8 12/31/2018Orangessecond1.8
1/2/2019Applesfirst1.9 1/1/2019Applesfirst1.5 1/1/2019Applesfirst1.5
1/2/2019Applessecond2 1/2/2019Applesfirst1.9 1/1/2019Applessecond1.6
1/2/2019Orangesfirst2.1 1/2/2019Applessecond2 1/1/2019Orangesfirst1.7
1/2/2019Orangessecond2.2 1/2/2019Orangesfirst2.1 1/1/2019Orangessecond1.8
1/3/2019Applesfirst2.3 1/2/2019Orangessecond2.2 1/2/2019Applesfirst1.9
1/3/2019Applessecond2.4 1/3/2019Applesfirst2.3 1/2/2019Applessecond2
1/3/2019Orangesfirst2.5 1/3/2019Applessecond2.4 1/2/2019Orangesfirst2.1
1/3/2019Orangessecond2.6 1/3/2019Orangesfirst2.5 1/2/2019Orangessecond2.2
1/4/2019Applesfirst2.7 1/3/2019Orangessecond2.6 1/3/2019Applesfirst2.3
1/4/2019Applessecond2.8 1/4/2019Applesfirst2.7 1/3/2019Applessecond2.4
1/4/2019Orangesfirst2.9 1/4/2019Applessecond2.8 1/3/2019Orangesfirst2.5
1/4/2019Orangessecond3 1/4/2019Orangesfirst2.9 1/3/2019Orangessecond2.6
     1/4/2019Orangessecond3 1/4/2019Applesfirst2.7
          1/4/2019Applessecond2.8
          1/4/2019Orangesfirst2.9
          1/4/2019Orangessecond3
2,149 Views
0 Likes
Brett_Bleess
Former Employee
Former Employee
‎2020-10-14 11:17 AM

@rubenmarin  any chance you have time to have a look at the most current post?  Your first reply got them part of the way there, they have another requirement they did not make clear in the initial post it seems...

Cheers,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
2,132 Views
0 Likes
rubenmarin
MVP
MVP
‎2020-10-14 11:28 AM

In response to Brett_Bleess

Hi @sarahshong , @Brett_Bleess I don't have time right now to reproduce, I would try to set "Order by CURVE, LABEL, DATE" instead of only DATE.

2,122 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2020-10-14 11:48 AM

@sarahshong  try below

 

 

Data:
LOAD 
     DATE1, 
     CURVE1, 
     LABEL1, 
     autonumber(DATE1&CURVE1&LABEL1) as Key,
     PRICE1
FROM Table;

AllData:
LOAD date(Min_Date+IterNo()-1) as DATE1
While Min_Date+IterNo()-1<=Max_Date;
LOAD min(DATE1) as Min_Date,
     max(DATE1) as Max_Date;
LOAD FieldValue('DATE1',RecNo()) as DATE1
AutoGenerate FieldValueCount('DATE1');

Left Join(AllData)
LOAD FieldValue('CURVE1',RecNo()) as CURVE1
AutoGenerate FieldValueCount('CURVE1');

Left Join(AllData)
LOAD FieldValue('LABEL1',RecNo()) as LABEL1
AutoGenerate FieldValueCount('LABEL1');

AllData_key:
LOAD *,
      autonumber(DATE1&CURVE1&LABEL1) as Key_All
Resident AllData;

DROP Table AllData;

Concatenate(Data)
LOAD *
Resident AllData_key
where not Exists(Key,Key_All);

DROP Table AllData_key;

Final:
LOAD DATE1, 
     CURVE1, 
     LABEL1, 
     if(isnull(PRICE1),Peek(PRICE1),PRICE1) as PRICE1
Resident Data
Order by CURVE1,LABEL1,DATE1;

DROP Table Data;

 

Screenshot 2020-10-14 164935.png

 

2,109 Views
rubenmarin
MVP
MVP
‎2020-10-14 12:15 PM

In response to rubenmarin

Hi @sarahshong , @Brett_Bleess ,I tested this code and seems to work I added a cartesian table to relate all date, curve and label:

 

Prices:
LOAD Date(Date#(DATE,'MM/DD/YYYY')) as DATE, CURVE, LABEL, PRICE INLINE [
    DATE, CURVE, LABEL, PRICE
    12/31/2018, Apples, first, 1.5
    12/31/2018, Apples, second, 1.6
    12/31/2018, Oranges, first, 1.7
    12/31/2018, Oranges, second, 1.8
    1/2/2019, Apples, first, 1.9
    1/2/2019, Apples, second, 2
    1/2/2019, Oranges, first, 2.1
    1/2/2019, Oranges, second, 2.2
    1/3/2019, Apples, first, 2.3
    1/3/2019, Apples, second, 2.4
    1/3/2019, Oranges, first, 2.5
    1/3/2019, Oranges, second, 2.6
    1/4/2019, Apples, first, 2.7
    1/4/2019, Apples, second, 2.8
    1/4/2019, Oranges, first, 2.9
    1/4/2019, Oranges, second, 3
];

TempTable_Rates:
LOAD Min(DATE) as minDate,
Max(DATE) as maxDate
resident Prices;

Let vMinDate = Num(Peek('minDate',0,'TempTable_Rates'));
Let vMaxDate = Num(Peek('maxDate',0,'TempTable_Rates'));

DROP table TempTable_Rates;

Date_Ranges:
LOAD
$(vMinDate) + IterNo()-1 as DateNum,
Date($(vMinDate) + IterNo()-1) as TempDate
AutoGenerate 1 while $(vMinDate) + IterNo()-1 <= $(vMaxDate);

Master_Calendar:
Load
TempDate as DATE,
Month(TempDate) as DATE_Month,
Year(TempDate) as DATE_Year
Resident Date_Ranges Order By TempDate ; // so that above values can be propagated downwards 

Drop Table Date_Ranges;

// Relations between all dates, curve and label
Cartesian:
LOAD FieldValue('DATE', RecNo()) as DATE AutoGenerate FieldValueCount('DATE');
Outer Join (Cartesian) LOAD FieldValue('CURVE', RecNo()) as CURVE AutoGenerate FieldValueCount('CURVE');
Outer Join (Cartesian) LOAD FieldValue('LABEL', RecNo()) as LABEL AutoGenerate FieldValueCount('LABEL');

// Join with prices, there should be all combinations, one with price as it was and others with price as null
Outer Join (Prices) LOAD * Resident Cartesian;
DROP Table Cartesian;

PricesFull:
NoConcatenate LOAD 
  DATE,
  CURVE,
  LABEL,
  If(IsNull(PRICE), Peek(PRICE), PRICE) as PRICE
Resident Prices
Order By CURVE,LABEL,DATE;

DROP Table Prices;

 

  

2,098 Views