topic Re: Create 1 calendar with 2 date tables in QlikView

Create 1 calendar with 2 date tables

pauldamen — Thu, 03 Apr 2014 08:23:45 GMT

Hi,

I have a script with a fact table and a different table with amounts. De fact table is linked to the Amount table with a ProjectID.

Also linked to the fact table is a master calendar based on the date field in the fact table. Now I have a date field in the Amount table as well (DateAmount).

How can I get this linked to the master calendar as well without creating loops etc? I searched the forum but couldn't find an answer that would help me fix it.

Thanks for your help!

Regards,

Paul

Re: Create 1 calendar with 2 date tables

arsal_90 — Thu, 03 Apr 2014 08:29:13 GMT

You can achieve this by link table

Re: Create 1 calendar with 2 date tables

rohit214 — Thu, 03 Apr 2014 08:33:55 GMT

hi,

do,

load

ID, Date,.... from facts;

left join

ID,

Date1 as Date

from amount fact;

so both the date come in single table.

hoope it helps you.

thanks

rohit

Re: Create 1 calendar with 2 date tables

crusader_ — Thu, 03 Apr 2014 08:35:23 GMT

Hi Paul,

Please check this very useful article http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Hope it helps.

Andrei

Re: Create 1 calendar with 2 date tables

pauldamen — Thu, 03 Apr 2014 08:41:34 GMT

Hi Rohit,

If I do that I get lots of sync keys:

Re: Create 1 calendar with 2 date tables

pauldamen — Thu, 03 Apr 2014 08:41:59 GMT

How do I create this link table with the sync keys I get below?

Re: Create 1 calendar with 2 date tables

arsal_90 — Thu, 03 Apr 2014 08:46:52 GMT

Below find the link table and if you still have problem then shared me your qvw with sample data

Re: Create 1 calendar with 2 date tables

crusader_ — Thu, 03 Apr 2014 08:48:33 GMT

Hi Paul,

To aviod Syn Keys you can create a table with all common fields inside, make a key using autonumber in parent tables and afterwards delete common fields from parent tables.

Re: Re: Create 1 calendar with 2 date tables

pauldamen — Thu, 03 Apr 2014 08:50:34 GMT

Thanks for the file.

Please find my file attached, hope you can help

Regards,

Paul

Re: Create 1 calendar with 2 date tables

rohit214 — Thu, 03 Apr 2014 08:51:51 GMT

hi,

can you share with me you code?

thanks

rohit

Re: Re: Create 1 calendar with 2 date tables

pauldamen — Thu, 03 Apr 2014 08:53:54 GMT

Hi Rohit,

You can find my full file attached to the post of Muhammad.

Thanks for your help

Regards,

Paul

Re: Create 1 calendar with 2 date tables

MK_QSL — Thu, 03 Apr 2014 09:04:17 GMT

Check enclosed file....

Re: Create 1 calendar with 2 date tables

arsal_90 — Thu, 03 Apr 2014 09:10:23 GMT

Please follow this document and i can't help if you don't provide the data.and you do the right thing but only you have to do is that in link part of your script you have to make flag and also comment date field in fact table. Hope this help

Re: Create 1 calendar with 2 date tables

mahesh_agrawal — Thu, 03 Apr 2014 09:12:14 GMT

Hi,

Please go through the changes made in the Facts tab in the script.

I think it will solve your issue.

Re: Re: Create 1 calendar with 2 date tables

pauldamen — Thu, 03 Apr 2014 09:29:39 GMT

Hi,

Not yet, is still lot of sync keys in there

Please find attached

Regards,

Paul

Re: Re: Re: Create 1 calendar with 2 date tables

mahesh_agrawal — Thu, 03 Apr 2014 09:53:46 GMT

Hi,

Please go through the changes made in the Facts tab in the script.

I think it will solve your issue.

Here I have droped ID and Date from Facts and AmountFacts table.

Now it will work.

Re: Re: Re: Re: Create 1 calendar with 2 date tables

pauldamen — Thu, 03 Apr 2014 10:02:07 GMT

Hi Manesh,

Thanks the structure looks good! But the output unfortunately not. If you look at the first tab of the dashboard (Prioritize) you see the listbox on the top left with a count, the total is 250 but I only have 50 projects so everything is times 5.

Also If you look at the table below you see every ID has 2 lines, one with the info and one with the budget (from the other table).

So something is not going correct with creating everything. Any ideas?

Regards,

Paul

Re: Re: Re: Re: Re: Create 1 calendar with 2 date tables

pauldamen — Thu, 03 Apr 2014 10:16:02 GMT

Hi,

I think I al most have it. Structure looks good.

But for some reason it won't match all the keys from the fact table to the link table. It will only do when they are the same. This is because of the Left Join I think.

But how can I solve it?

Regards,

Paul

Re: Re: Re: Re: Re: Create 1 calendar with 2 date tables

pauldamen — Thu, 03 Apr 2014 11:49:53 GMT

Any ideas guys?