Creating a master calendar and filling in missing ... - Qlik Community

sarahshong · ‎2020-09-19

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;

rubenmarin · ‎2020-09-20

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;

sarahshong · ‎2020-09-20

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?

DATE

CURVE

LABEL

PRICE

DATE

CURVE

LABEL

PRICE

DATE

CURVE

LABEL

PRICE

12/31/2018

Apples

first

1.5

12/31/2018

Apples

first

1.5

12/31/2018

Apples

first

1.5

12/31/2018

Apples

second

1.6

12/31/2018

Apples

second

1.6

12/31/2018

Apples

second

1.6

12/31/2018

Oranges

first

1.7

12/31/2018

Oranges

first

1.7

12/31/2018

Oranges

first

1.7

12/31/2018

Oranges

second

1.8

12/31/2018

Oranges

second

1.8

12/31/2018

Oranges

second

1.8

1/2/2019

Apples

first

1.9

1/1/2019

Apples

first

1.5

1/1/2019

Apples

first

1.5

1/2/2019

Apples

second

2

1/2/2019

Apples

first

1.9

1/1/2019

Apples

second

1.6

1/2/2019

Oranges

first

2.1

1/2/2019

Apples

second

2

1/1/2019

Oranges

first

1.7

1/2/2019

Oranges

second

2.2

1/2/2019

Oranges

first

2.1

1/1/2019

Oranges

second

1.8

1/3/2019

Apples

first

2.3

1/2/2019

Oranges

second

2.2

1/2/2019

Apples

first

1.9

1/3/2019

Apples

second

2.4

1/3/2019

Apples

first

2.3

1/2/2019

Apples

second

2

1/3/2019

Oranges

first

2.5

1/3/2019

Apples

second

2.4

1/2/2019

Oranges

first

2.1

1/3/2019

Oranges

second

2.6

1/3/2019

Oranges

first

2.5

1/2/2019

Oranges

second

2.2

1/4/2019

Apples

first

2.7

1/3/2019

Oranges

second

2.6

1/3/2019

Apples

first

2.3

1/4/2019

Apples

second

2.8

1/4/2019

Apples

first

2.7

1/3/2019

Apples

second

2.4

1/4/2019

Oranges

first

2.9

1/4/2019

Apples

second

2.8

1/3/2019

Oranges

first

2.5

1/4/2019

Oranges

second

3

1/4/2019

Oranges

first

2.9

1/3/2019

Oranges

second

2.6

1/4/2019

Oranges

second

3

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

Brett_Bleess · ‎2020-10-14

@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.

rubenmarin · ‎2020-10-14

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.

Kushal_Chawda · ‎2020-10-14

@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;

rubenmarin · ‎2020-10-14

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;

Creating a master calendar and filling in missing data

master calendar

Missing Data