topic Re: Joining calendar to Fact table in QlikView

Joining calendar to Fact table

Tue, 17 Mar 2015 20:20:34 GMT

Hi Everyone

I am building a model and struggling with linking the dates data to my fact table.

I have used Year(DateField)*100+ Month(DateField) to join my calendar and fact tables but the data doesn't seem to have a relationship.

Please assist. I have attached my app and data files

Re: Joining calendar to Fact table

jduenyas — Tue, 17 Mar 2015 20:30:19 GMT

Set you YearMonth as

MakeDate(Year(FIN_YEAR_ID),Month(FIN_YEAR_ID),Day(FIN_YEAR_ID))

Do the same with other data from other tables so that the date format on all date values is the same (MM/DD/YYYY without Hours and Minutes -- if that is your requirement)

Re: Joining calendar to Fact table

hic — Tue, 17 Mar 2015 20:37:28 GMT

You define your key incorrectly...

You should probably use

FIN_YEAR_ID*100+Month(FIN_YEAR_ID) as YearMonth,

instead of

Year(FIN_YEAR_ID)*100+Month(FIN_YEAR_ID) as YearMonth,

Re: Joining calendar to Fact table

Tue, 17 Mar 2015 20:54:42 GMT

Hi Henric

I have changed the field expression as per your suggestion and the data still doesn't link.

Can you perhaps upload the app if you can get it right on your side?

Re: Joining calendar to Fact table

hic — Wed, 01 Apr 2015 12:04:06 GMT

You have concatenated the facts with Customers and with Items. I have loaded these in three different tables. Also, you had too many keys, so I have removed the redundant ones. Finally, I changed the definition of YearMonth.

See attachment.

HIC