Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

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
Esteemed Contributor

Re: Question on link table

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
Contributor

Re: Question on link table

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

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

datanibbler
Esteemed Contributor

Re: Question on link table

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
Contributor

Re: Question on link table

could you attach your example?

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

datanibbler
Esteemed Contributor

Re: Question on link table

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

Community Browser