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: 
datanibbler
Champion
Champion

Question on link table

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

5 Replies
datanibbler
Champion
Champion
Author

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

angelaecheverri
Creator
Creator

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

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

datanibbler
Champion
Champion
Author

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

angelaecheverri
Creator
Creator

could you attach your example?

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

datanibbler
Champion
Champion
Author

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