Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sifatnabil
Specialist
Specialist

Master calendar with 2 fact tables

Hi all,

I have 2 fact tables, PricingReport and TradeReport - not all date values in PricingReport exist in TradeReport. Therefore, I would like to create a master calendar which covers all dates. I can create the calendar for 1 fact table (e.g. PricingReport) like below, but how can I add it also for TradeReport?

tmpMinMaxDates:

LOAD
MIN(Num(Date)) AS MinDate,
Max(Num(Date)) AS MaxDate
RESIDENT
PricingReport;

LET varMinDate = PEEK('MinDate');
LET varMaxDate = PEEK('MaxDate');

DROP TABLE [tmpMinMaxDates];

tmpCalendar:
LOAD
DATE($(varMinDate) + RowNo() - 1) AS Date
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

Left Join

Calendar:
LOAD
   
day(Date) as DayNumber,
WeekDay(Date) as Day,
month(Date) as Month,
Year(Date) as Year,
date(WeekEnd(Date,0,-2),'D/M') as Week
Resident tmpCalendar;

1 Solution

Accepted Solutions
Not applicable

Maybe you should consider to concatenate or join Pricing and Trade reports into one table?

View solution in original post

6 Replies
Not applicable

Maybe you should consider to concatenate or join Pricing and Trade reports into one table?

Not applicable

Concatanate these two fact tables and then link it with master calender with the date field.

MayilVahanan

Hi

Why you can try like this

Take min and max value both the table and from that, you can able to find the minimum and maximum values among those values.

Then you can generate the calendar based on that.

i.e. Common calendar for both the table by using minimum and maximum date range.

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Gysbert_Wassenaar

Try using the FieldValue and FieldValueCount functions:

Temp:

LOAD

     min(FieldValue('Date', recno())) as MinDate,

     max(FieldValue('Date', recno())) as MaxDate

AUTOGENERATE FieldValueCount('Date');

LET varMinDate = PEEK('MinDate');

LET varMaxDate = PEEK('MaxDate');

As long as your fact tables use the same name for the Date field you will get the min and max date over both fact tables.


talk is cheap, supply exceeds demand
amit_saini
Master III
Master III

Hi Sifat,

Call these fields by a single name for example here in this case i m considering name as Datefield.

You this scrip for master calender:

LET vMinDate = num(makedate(2005));

LET vMaxDate = num(today());

Datefield:

LOAD

    $(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

    Datefield as %Date,

    date(Datefield) as Date,

    year(Datefield) as Year,

    month(Datefield) as Month,

    day(Datefield) as Day,

    week(Datefield) as Week,

    weekday(Datefield) as Weekday

RESIDENT Datefield;

drop table Datefield;

Thanks,

AS

Not applicable

how to create peek varible