Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Not applicable

Re: Master calendar with 2 fact tables

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

View solution in original post

6 Replies
Highlighted
Not applicable

Re: Master calendar with 2 fact tables

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

View solution in original post

Highlighted
Not applicable

Re: Master calendar with 2 fact tables

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

Highlighted

Re: Master calendar with 2 fact tables

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Master calendar with 2 fact tables

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
Highlighted
Master III
Master III

Re: Master calendar with 2 fact tables

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

Highlighted
Not applicable

Re: Master calendar with 2 fact tables

how to create peek varible