topic Re: 1 fact table, 2 date dimensions, 1 master calendar in QlikView

1 fact table, 2 date dimensions, 1 master calendar

amien — Tue, 10 May 2011 07:40:27 GMT

the situation:

1. a fact table that contains two date dimensions (first created date and departure date)

2. 1 master calendar

3. a linked table.

the master calendar can only link to 1 date dimension in the fact table. how can i link the master calender to both date dimensions? the only solution that i see is to read the fact tables twice and create two fact tables. is that the best solution?

1 fact table, 2 date dimensions, 1 master calendar

Tue, 10 May 2011 11:56:22 GMT

Hi Amien,

whats the purpose of the master calendar? in other words, what should happen if i select may 10th 2011?

Regards,

Reyman

1 fact table, 2 date dimensions, 1 master calendar

amien — Tue, 10 May 2011 12:26:32 GMT

i have several fact tables. All the other fact tables only contains 1 date dimension.

purpose of master calendar:

* when you select 10th may 2011 .. you well see all the data of that day of each fact table, makes it possible to compare

* when i have a bar chart and a certain data doesnt contain any data, when my bar chart will miss days. i want to see the 0 values dates in my bar chart. I need a master calendar for that.

1 fact table, 2 date dimensions, 1 master calendar

Tue, 10 May 2011 12:35:23 GMT

what i meant was, in the one fact table containing the 2 date fields, must they both be May 10, or just one of them? what are those dates supposted to mean?

1 fact table, 2 date dimensions, 1 master calendar

amien — Tue, 10 May 2011 12:43:10 GMT

i have now the fact table, which contains 2 fields with date (created and departure), linked to the master calender (via link table) on only 1 date (created). but i want in my pivot chart to both analyse the values on created date and departure date. including link to the master calender. in my opinion this can only be achieved to read the same fact table twice. one table will use the created date, the other table will use the departure date.

i was wondering if there is a different solution besides reading the fact table twice.

1 fact table, 2 date dimensions, 1 master calendar

Tue, 10 May 2011 12:48:29 GMT

You can try by linking the one date to the master calendar and the other in an expression with set analysis code linking dynamic to the the selected date in the master calendar.

1 fact table, 2 date dimensions, 1 master calendar

amien — Tue, 10 May 2011 12:55:02 GMT

sounds good .. do you have an example?

1 fact table, 2 date dimensions, 1 master calendar

Tue, 10 May 2011 13:05:05 GMT

HI,

i tried it with something like this.

Sum (

{1<

FactTable.Date1={$(=Max(Calendar.CalDate))},

FactTable.Date2={$(=Max(Calendar.CalDate))},

Amount

)

Assumed that the dateformat is the same for all the different date fields, you can use this,

In this case you get all the Amounts when Date1 and Date2 are equal to the maximum value of CalDate.

You can look into the helpfile for more examples of the Set Analysis code. The basics are explained there.

I hope this is close to what you are looking for.

Regards,

Reyman

1 fact table, 2 date dimensions, 1 master calendar

Anonymous — Tue, 10 May 2011 13:41:04 GMT

Using a seperate table (not coupled to other tables) for one of the date selections and then using Set Analysis works good for most of these situations.

When using 2 date selections on one date (start and ending date), this will not work ... since one selection rules out the other one. In this case you should consider adding 2 seperate date tables and only use set analysis (with big data amounts this is not really good for the performance!!).

Another way is doing some pre-loading in the LOAD script to add some extra data (link table or whatever you want) which contains the date combinations possible.

1 fact table, 2 date dimensions, 1 master calendar

amien — Sun, 15 May 2011 21:33:29 GMT

@reyman

SUM({1<departuredate={">=$(=MIN(DateYYYYMMDD))<=$(=MAX(DateYYYYMMDD))"}>} DISTINCT value)

i'm using this one right now .. works great .. but only when the user selects 1 year and a few weeks/days.

i have a problem when a user selects 2 years and 1 weeknumber. i want to see the results of only week 2010-16 and 2011-16. now i get the range between 2010-16 and 2011-16

your set analysis i can't get it working

1 fact table, 2 date dimensions, 1 master calendar

amien — Sun, 15 May 2011 22:03:57 GMT

i'v added week also to the set analysis

Re: 1 fact table, 2 date dimensions, 1 master calendar

Tue, 17 May 2011 15:22:15 GMT

Hi Amien,

would the attached example work for you?

Now it uses the selected values based on both the fields (created & departuredate)

Regards,

Reyman

Re: 1 fact table, 2 date dimensions, 1 master calendar

johnw — Tue, 17 May 2011 20:16:54 GMT

You can link multiple dates to a generic calendar by using a linkage table.

LOAD
ID
,CreatedDate as Date
,'Created' as DateType
RESIDENT Data
;
CONCATENATE (Link)
LOAD
ID
,DepartureDate as Date
,'Departure' as DateType
RESIDENT Data
;

See attached example. In the example, I leave the original date fields in place so that you can see the raw data. However, you may want to remove them in the actual application, as having too many date fields can be confusing when it comes time for users to make selections.

Re: 1 fact table, 2 date dimensions, 1 master calendar

Fri, 12 Oct 2012 14:11:32 GMT

Hi all

I think I would need your help. I'm very new on QV and I'm facing some problems that should be easy for you guys who work with QV for a long time...

Well, I cant understand the most of the examples that are in the community so I need some costum help for my problem... So, I have some tables (in each table I have 2 or more date fields) and I want to have a single master calendar for every date field in order to be able to have, for example, a chart and can filter by year. How can I do that?

I have another issue, I cant open your .qvw samples cause I'm running the Personal Edition and i no longer can open external files...

I hope you can help me...

Kind regards,

TMF

Re: 1 fact table, 2 date dimensions, 1 master calendar

johnw — Fri, 12 Oct 2012 19:14:33 GMT

tmendesf wrote:
Hi all
...I have some tables (in each table I have 2 or more date fields) and I want to have a single master calendar for every date field in order to be able to have, for example, a chart and can filter by year. How can I do that?...

I'll post my most recent example of this sort of thing, which handles multiple dates from multiple tables as requested, so it may be what you need. I say "may" because this approach applied to multiple tables CAN cause loops in the data structure, in which case you'll need either underlying data model changes or just a completely different solution (such as the island calendar, also discussed below). Since you can't bring up the example, I'll also give you the script and the chart definitions below.

Here's the script:

SET DateFormat='YYYY-M-D';

Table1:
LOAD * INLINE [
ID, Seq, ActivityDate, Value1
1, 1, 2011-5-10,10
1, 2, 2011-5-12,2
2, 1, 2011-5-12,20
3, 1, 2011-6-15,40
];

Table2:
LOAD recno() as ID, * INLINE [
DepartureDate, ArrivalDate, Value2
2011-6-15,2011-6-16,5
2011-6-15,2011-6-17,10
2011-6-16,2011-6-16,20
];

OtherTable:
LOAD recno() as OtherID, * INLINE [
OtherDate, OtherValue
2011-5-12,2
2011-6-15,3
2011-6-17,5
];

Link:
LOAD
ID
,Seq
,ActivityDate as Date
,'Activity' as DateType
RESIDENT Table1
;
CONCATENATE (Link)
LOAD
ID
,DepartureDate as Date
,'Departure' as DateType
RESIDENT Table2
;
CONCATENATE (Link)
LOAD
ID
,ArrivalDate as Date
,'Arrival' as DateType
RESIDENT Table2
;
CONCATENATE (Link)
LOAD
OtherID
,OtherDate as Date
,'Other' as DateType
RESIDENT OtherTable
;

Calendar:
LOAD *
,date(monthstart(Date),'MMM YYYY') as Month
;
LOAD date(makedate(2011,5,1)+recno()-1) as Date
AUTOGENERATE 61
;

IslandCalendar:
LOAD *
,date(monthstart(IslandDate),'MMM YYYY') as IslandMonth
;
LOAD date(makedate(2011,5,1)+recno()-1) as IslandDate
AUTOGENERATE 61
;

So in the script above, Table1, Table2 and OtherTable are my actual data tables. The intent of the rest of the script is to add to this model without changing it, and for the additions to handle the linkage to a single generic calendar. The Link table is where all this linkage happens, where all the magic happens, and then the Calendar table is the generic calendar. The IslandCalendar table is for the alternative I mentioned, which is probably a much more common solution to this kind of problem.

My example has three example charts in it. The first is a straight table:

Dimension: Date
Expression 1: sum({<DateType={'Activity'}>} Value1) // set analysis needed because ID is shared
Expression 2: sum({<DateType={'Departure'}>} Value2) // set analysis needed because ID is shared
Expression 3: sum(OtherValue) // set analysis not needed because OtherID not shared

The second chart is exactly the same except for using Month as a dimension.

The third chart is a pivot table defined like this:

Dimension 1: ID
Dimension 2: DepartureDate
Dimension 3: ArrivalDate
Dimension 4: ActivityDate
Expression 1: sum(Value1)
Expression 2: if(dimensionality()=1,sum(Value2))

Then we have the island calendar alternative. As stated in a text box on the sheet:

"An island calendar is disconnected from the rest of your data. It's a possible alternative if using the data model solution above causes loops in your data structure. The disadvantage is largely performance, as every expression must use an if() to link the island date back to whatever date you actually care about, which can be a very slow process compared to direct links in the data model. The expressions would typically be more complex with an island calendar as well. In the example, the expressions are of similar complexity, but that's because our example is particularly complex, sharing an ID across multiple tables that each have dates. This is probably atypical.

The bottom example chart is the same either way, as it is referring directly to the underlying dates rather than either of our master calendars."

The first chart with the island calendar is defined like this:

Dimension: IslandDate
Expression 1: sum(if(ActivityDate=IslandDate,Value1))
Expression 2: sum(if(DepartureDate=IslandDate,Value2))
Expression 3: sum(if(OtherDate=IslandDate,OtherValue))

The second chart is just like the first but with IslandMonth as the dimension. And the third chart is exactly the same as the third chart with the previous solution.

Re: 1 fact table, 2 date dimensions, 1 master calendar

Mon, 15 Oct 2012 08:19:09 GMT

Hi John

Thanks for your post. However, I just went through another solution because I was running out of time last friday. Therefore, when I have some time I will test your code because it can be very useful for my work. When I test it, I'll give you my feedback.

Thank you again for answer me

Best regards,

TMF

Re: 1 fact table, 2 date dimensions, 1 master calendar

Anonymous — Mon, 15 Oct 2012 08:22:21 GMT

Hi Amien,

Can you provide test for me

Shaik

Re: 1 fact table, 2 date dimensions, 1 master calendar

Anonymous — Mon, 15 Oct 2012 08:30:42 GMT

Hi Amien,

do one thing

test:

LOAD ID,

CreatedDate as LinkDate,

CreatedDate,

DepartureDate
RESIDENT Data;

CONCATENATE (test)

LOAD ID,

DepartureDate as LinkDate,

CreatedDate,

DepartureDate

RESIDENT Data;

drop table Data;

// create a calendar

load Date AS LinkDate,

month,

year,

Days

from Calendar.qvd(qvd);

above transactions both will be concatenated and association with Calendar now.

please check and inform to me

SHAIK