topic Difficulty in Calendar Link Table and Calendar in QlikView

Difficulty in Calendar Link Table and Calendar

Fri, 20 Apr 2012 03:10:39 GMT

Hi,

I want to show 3 reports on the basis of 3 different date fields which are present in 3 different tables and i want to connect my calendar field to these 3 fields seperately for that i used one more table called calendar Link in which i am bringing all the primary keys of these 3 tables and date field of master calendar table this concept is not working in my case because 3 tables are connected with some relation and while doing this method, it generates loops ..... can any one tel me how to avoide these loops or any other method to that ?

yojas

Difficulty in Calendar Link Table and Calendar

johnw — Sat, 21 Apr 2012 00:32:42 GMT

In some cases you might be able to concatenate the tables into one with some sort of "type" field distinguishing them, and use the same date field for all three that way. That's not always practical, of course.

Difficulty in Calendar Link Table and Calendar

Mon, 23 Apr 2012 02:53:49 GMT

Hi,

Yes even i concatenated two tables but the thired one is not directly linked to first one but it is linked via one more table... then in that case it difficult to concatinate all tables togather.....

and you told me to do concatenation on the basis of some type.... can you explain more about that?

-yojas

Difficulty in Calendar Link Table and Calendar

Mon, 23 Apr 2012 05:30:26 GMT

Hi,

Make a calender table by concatenating those 3 date fields and keep it unlinked. Write expressions in those charts by using this coomcalender datefields. If u tell me what chart u r making i can tell u the expression also.

Re: Difficulty in Calendar Link Table and Calendar

johnw — Mon, 23 Apr 2012 16:45:30 GMT

I meant something like this:

Type,     ID,     Customer, Product, Date,     Order Date, Shipment Date, Invoice Date
Order,    ORD456, Bob     , Apples , 20120417, 20120417 ,              ,
Shipment, SHP789, Bob     , Apples , 20120422,           , 20120422     ,
Invoice, INV123, Bob     , Apples , 20120423,           ,              , 20120423

Though really, this is probably also an example of why this may not always be practical. We might, for instance, want to connect the shipment and the invoice back to the order. When they're all in the same table like this, that's not easily done.

A common approach is to create a date island, just a calendar disconnected from all your data. Then use the island date as a dimension, and do things like sum(if("Order Date"="Island Date", "Amount")). The main problem with that approach is poor performance on large data sets, though it works just fine on smaller data sets. I suspect that's what Sweth_24 was suggesting, though I can't quite tell.

Re: Difficulty in Calendar Link Table and Calendar

Tue, 24 Apr 2012 04:14:08 GMT

Hi John

Yes thats what i was saying