topic Re: Question on link table in QlikView

Question on link table

datanibbler — Thu, 05 Dec 2013 12:28:23 GMT

Hi,

in one of my apps displaying personell_data, I have now introduced a link_table linking six or so tables where the personell_ID and the corresp. mapped fields for plant and area are always the same.

The individual data tables are all linked to that via a compound key made up of (personell_ID&'|' Date).

Only one table is linked merely via the date because in that table the mapped_fields for plant and area would not make sense.

Currently, however, what I do is load RESIDENT from all of the data tables and concatenating all those RES. LOADs into one large link_table.

=> I would like to join them instead to have a much shorter link_table

<=> I am unsure about that because the size of the tables does differ insofar as for some tables I take into account all employees and for others I take into account only the hourly employees, which are approx. 100 fewer.

- other than that, the fields I extract into the mapping table are always the same - identical even, to all probability . but for the nr. of records.

=> Every record in the linking table would then have a date, a personell_ID, several keys (one for each data table) and the mapped_fields for plant and area.

=> Will that difference in the nr. of records in the data tables lead to problems or can I replace my concatenations by JOINs?

(I guess with a LEFT JOIN, I would just get blanks - one blank keyfield, that would be - in the records which I don't have in that particular data_table, no? That wouldn't be a problem, would it?)

Thanks a lot!

Best regards,

DataNibbler

Re: Question on link table

datanibbler — Thu, 05 Dec 2013 13:06:32 GMT

Hi,

I have another question rgd. that link_table:

- Before, I used to have a COUNT(DISTINCT Pers_ID) in my chart (counting the pers_ID from that data_table

<=> Now the data_table does not have that field anymore. It is in the link_table

=> Can somebody help me with the syntax for the formula to say, like

"Count pers_ID in link_table where the keyfield in that row is not empty"?

I guess I need a set_expression, but that is going to be rather complicated as I guess there's going to be trouble when I try nesting different sets of quotes...

Thanks a lot!

Best regards,

DataNibbler

Re: Question on link table

angelaecheverri — Thu, 05 Dec 2013 13:33:32 GMT

is better for you use count id from you link table.

in order that this table contains all the possible records...

Re: Question on link table

datanibbler — Thu, 05 Dec 2013 13:39:25 GMT

Hi Angela,

I tried that and it does look very plausible on the dimension "day" - but, due to the fact that

there are many more DISTINCT values of the keyfield which consists of Pers_ID&Date (as compared to Pers_ID),

this does not work at all on the dimension "month" (where I use the aggr() function)

Do you have another idea how to do this properly?

Thanks a lot!

Best regards,m

DataNibbler

Re: Question on link table

angelaecheverri — Thu, 05 Dec 2013 14:37:55 GMT

could you attach your example?

i am not following you what do you real need....

Re: Question on link table

datanibbler — Thu, 05 Dec 2013 14:51:43 GMT

Hi Angela,

well, an example is always kind of tricky (this app is using personell_data) and it takes a while to prepare. I'll be back, though.

Talk to you soon!

Best regards,

DataNibbler