Re: Two datefields and one master calendar - Page 2 - Qlik Community

Report Inappropriate Content · ‎2012-08-22

Hi everybody.

I have one fact table with two datefields, ChangeDate and StockDate, and a master calendar. Currently I'm using one of the datefileds to link to the master calendar, Left(ChangeDateID, 6) as MonthID, and it works fine to use a calendar in my application and choose between different dates.

But, I would like to have two calenders in my applications, one with ChangeDate and another one with StockDate.

Should I create two calendars? Is there anyone that have any example to share?

sebastiandperei · ‎2012-08-23

What happens with the solution i has provided? If you don't tell us, is very difficult...

sebastiandperei · ‎2012-08-23

The script i sent make the same result you sent...

Im confused... what do you need?

Report Inappropriate Content · ‎2012-08-24

I didn't get your script working. Do you have a qvw example including the script?

sebastiandperei · ‎2012-08-24

I've attached your qvw with my suggestions.

sebastiandperei · ‎2012-08-24

Reload it.

If it gives you an error, send the error or send the file to I check the reload.

Anonymous · ‎2012-11-26

Hello Masha,

I like your solution but I do see an issue with it.

How do you handle a date being both valid for Change Date and Stock Date? What type is it going to be? Don't you run the risk of excluding dates?

Thanks

masha-ecraft · ‎2012-11-27

Benoit,

If a date is both Change Date and Stock Date, it is going to have both types.

The Calendar table will look like the following:

CalendarKey DateType Month

'Nov2012/Nov2012' 'Change Date' 'Nov 2012'

'Nov2012/Nov2012' 'Stock Date' 'Nov 2012'

Report Inappropriate Content · ‎2012-11-27

i think this is u want,ple let me know friend

date1:

LOAD date(date#(ChangeDateID,'YYYYMMDD'),'DD/MM/YYYY') as ChangeDateID,

(MonthID) as MonthID ,

date(date#(StockDateID,'YYYYMMDD'),'DD/MM/YYYY') as StockDateID

FROM

(ooxml, embedded labels, table is TB02_20120823_120900);

quarter_map:

mapping LOAD RowNo() as month,

'Q' & Ceil(RowNo()/3) as Quarter

AutoGenerate (12);

temp:

LOAD max(StockDateID) as max,

min(StockDateID) as min

Resident date1;

let mindate=num(Peek('min',0,'temp'));

let maxdate=num(Peek('max',0,'temp'));

DROP Table temp;

tep:

LOAD $(mindate)+IterNo()-1 as num,

date($(mindate)+IterNo()-1) as date

AutoGenerate 1 While $(mindate)+IterNo()-1<=$(maxdate);

QUALIFY *;

UNQUALIFY StockDateID;

mastercalender:

LOAD date as StockDateID,

Year(date) as Year,

ApplyMap('quarter_map',month(date),null()) as Quarter

Resident tep

Order by date;

UNQUALIFY *;

QUALIFY *;

UNQUALIFY ChangeDateID;