topic Two datefields and one master calendar in QlikView

Two datefields and one master calendar

Wed, 22 Aug 2012 05:42:32 GMT

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?

Re: Two datefields and one master calendar

sreenivas — Wed, 22 Aug 2012 06:14:03 GMT

Hi Try this

Data:

Load * Inline

[

StockDate,ChangeDate

08/20/2012,08/21/2012

08/10/2012,08/11/2012

08/15/2012,08/14/2012

07/14/2012,07/21/2012

08/13/2012,08/19/2012

08/12/2012,08/18/2012

08/11/2012,08/17/2012

08/10/2012,08/16/2012

];

CommonDate:

LOAD StockDate as DAte Resident Data;

Concatenate

LOAD ChangeDate as DAte Resident Data;

Re: Two datefields and one master calendar

Wed, 22 Aug 2012 06:55:51 GMT

Hi,

I think you want to have a single calendar which should reflect the data for the other two dates aswell.

So, you can create a link table between your fact table and the master calendar.

This link table will inclued both the dates concatenated or joned as a single name filed called Date which will get link to master calendar.Also you need to create a synthetic key with change date and stock date which will link the fact table and link table.

Re: Two datefields and one master calendar

masha-ecraft — Wed, 22 Aug 2012 07:19:39 GMT

Hi,

Add a field to your fact table:

MonthName(ChangeDate) & '/' & MonthName(StockDate) as CalendarKey

Then create a new master calendar:

Calendar:

LOAD

MonthName(ChangeDate) & '/' & MonthName(StockDate) as CalendarKey,

'Change Date' as DateType,

MonthName(ChangeDate) as Month

Resident FactTable;

Concatenate (Calendar)

LOAD

MonthName(ChangeDate) & '/' & MonthName(StockDate) as CalendarKey,

Stock Date' as DateType,

MonthName(StockDate) as Month

Resident FactTable;

You can create a list box for DateType field so the user can select which date he is using.

/Masha

Re: Two datefields and one master calendar

Thu, 23 Aug 2012 11:19:29 GMT

Do you have any example on have you suggest to solve the issue?

I've attached a example on my issue.

Re: Two datefields and one master calendar

Thu, 23 Aug 2012 12:16:54 GMT

Hi,

Please find the attached application.Reload it at ur end as i was not having the excel file required to reload.

Re: Two datefields and one master calendar

sebastiandperei — Thu, 23 Aug 2012 12:52:20 GMT

Hi,

Note that you are linking about months, and leave out days.

If you want to have two different calendars, and link by day, the script could be:

Contract:

LOAD ChangeDateID,

StockDateID

FROM

(ooxml, embedded labels, table is TB02_20120823_120900);

Stock_Calendar:

NoConcatenate

Load Distinct

StockDateID,

MakeDate(Left(StockDateID,4), num(Mid(StockDateID,5,2)),Right(StockDateID,2)) as ST_Date

Resident Contract;

Left Join

Load

ST_Date,

Month(ST_Date) as ST_Month,

Year(ST_Date) as ST_Year,

Day(ST_Date) as ST_Year,

Ceil(Month(ST_Date)/4) as ST_Quarter

Resident Stock_Calendar;

Change_Calendar:

Load Distinct

ChangeDateID,

MakeDate(Left(ChangeDateID,4), num(Mid(ChangeDateID,5,2)),Right(ChangeDateID,2)) as CH_Date

Resident Contract;

Left Join

Load

CH_Date,

Month(CH_Date) as CH_Month,

Year(CH_Date) as CH_Year,

Day(CH_Date) as CH_Year,

Ceil(Month(CH_Date)/4) as CH_Quarter

Resident Stock_Calendar;

Now, you will get the different fields for each date (Stock and Change)

Note that when you will select one date, the other dates will be filter to the registers with the date you have selected.

Re: Two datefields and one master calendar

Thu, 23 Aug 2012 13:41:36 GMT

It still doesn't working successfully. And how do I manage to use two calendar objects with different fields?

Re: Two datefields and one master calendar

Thu, 23 Aug 2012 13:42:23 GMT

See attached files.

Re: Two datefields and one master calendar

Thu, 23 Aug 2012 14:20:20 GMT

The functionallity I'm looking for is like attached qvw. But this is maybe not the best way to do it.

Re: Two datefields and one master calendar

sebastiandperei — Thu, 23 Aug 2012 21:51:07 GMT

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

Re: Two datefields and one master calendar

sebastiandperei — Thu, 23 Aug 2012 21:52:00 GMT

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

Im confused... what do you need?

Re: Two datefields and one master calendar

Fri, 24 Aug 2012 06:35:48 GMT

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

Re: Two datefields and one master calendar

sebastiandperei — Fri, 24 Aug 2012 14:14:20 GMT

I've attached your qvw with my suggestions.

Re: Two datefields and one master calendar

sebastiandperei — Fri, 24 Aug 2012 14:15:21 GMT

Reload it.

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

Re: Two datefields and one master calendar

Anonymous — Mon, 26 Nov 2012 20:54:27 GMT

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

Re: Two datefields and one master calendar

masha-ecraft — Tue, 27 Nov 2012 06:40:39 GMT

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'

Re: Two datefields and one master calendar

Tue, 27 Nov 2012 09:01:49 GMT

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;

mastercalender:

LOAD date as ChangeDateID,

Year(date) as Year,

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

Resident tep

Order by date;

DROP Table tep;

UNQUALIFY *;

Re: Two datefields and one master calendar

Anonymous — Tue, 27 Nov 2012 14:22:41 GMT

I see; that makes sense. I missed the use of Concatenate here.

Thanks