Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Master Calender with 4 date fields

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

7 Replies
Not applicable
Author

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;

johnw
Champion III
Champion III

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.

Not applicable
Author

Use the linktable concept.

Not applicable
Author

@ 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

Anonymous
Not applicable
Author

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

johnw
Champion III
Champion III

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

Anonymous
Not applicable
Author

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,