topic Master Calender with 4 date fields in QlikView

Master Calender with 4 date fields

Mon, 23 May 2011 15:49:28 GMT

Hi everybody,

I have read so much through this community, but I didn't find an answer to my question. I want to use list boxes for the date for the easy choice.

I have tables with different datafields. In all of them is the key %Item, because I have to combine different tables over this field. So I could not make all date fields as key words, because I then have many synthetic keys =(

For example only four tables: Purorder, Order, Stocks and Prognosis.

The stocks shows me the past development. The prognosis the future based on the order.

Now I want to make a master calender, which shows me all datas, from past over actual to future.

I tried this:

date:

LOAD

purorder.date AS %date

RESIDENT purorder;

JOIN LOAD

order.date AS %date

RESIDENT order;

JOIN LOAD

stocks.date AS %date

RESIDENT stocks;
JOIN LOAD

prognosis.date AS %date

RESIDENT prognosis;

The Calender shows me now, all dates from purorder. I have also try to use the concatenate respect the JOIN, but the result ist the same. Has anyone an idea, where my mistake lies?

Regards

Master Calender with 4 date fields

Mon, 23 May 2011 16:01:45 GMT

Hi,

If I have understood your problem, what you want is to add data to date table. Join will not work, because you need at least a field to relate both tables apart from the date.

I think it would work something like:

date:

LOAD

purorder.date AS %date

RESIDENT purorder;

LOAD

order.date AS %date

RESIDENT order;

LOAD

stocks.date AS %date

RESIDENT stocks;
LOAD

prognosis.date AS %date

RESIDENT prognosis;

Re: Master Calender with 4 date fields

johnw — Mon, 23 May 2011 23:48:02 GMT

I'm not sure I understood the question either, but if so, see the attached example. It's not exactly your situation, but it connects three dates from three tables to the same master calendar. Two of the tables use the same ID, while the third uses a different ID. I guess ignore the third table, and you have something very like your situation.

Re: Master Calender with 4 date fields

Tue, 24 May 2011 04:34:15 GMT

Use the linktable concept.

Master Calender with 4 date fields

Tue, 24 May 2011 06:18:22 GMT

@ John,

Thanks a lot. After some loading problems I find out the right way. Now all dates are in the calender table.

@ a.medina

Now without the mistakes in my database, your way also works fine. thx

Re: Master Calender with 4 date fields

Anonymous — Thu, 08 Mar 2012 11:14:09 GMT

What if the two datafields namely "Date Purchased" and "Waranty Expiry Date" are all in one Fact Table called Fact.How then do you script because the sample attachment is challenging for me.

Thanks

Re: Master Calender with 4 date fields

johnw — Thu, 08 Mar 2012 16:54:54 GMT

Attached is an updated example with two date fields in a single table (Table2) among other things. I guess ignore Table1 and OtherTable.

Re: Master Calender with 4 date fields

Anonymous — Thu, 30 Aug 2012 07:27:44 GMT

I am able to get now the master calender from all the fields, but now the proble is to keep a from date and Todate as selection criteriea in from end.Please help me in this. Best Regards,