topic Re: linking two tables in QlikView

linking two tables

learnerqlik53 — Sat, 16 Nov 2024 19:06:47 GMT

Hi,

I want two link two table.

Table1:
Month, Employee Id, Country, Age, Region

Table2:

Submitted date, Recipient Id, Recipient Country, Recipient Age, Recipient Region, Submitter Id, Submitter Age, Submitter Country,Submitter Region

I have to calculate KPI on both Submitter and Recipient separately. Employee ID and Submitter Id and Recipient ID will have similar values.

Table1 is like total headcount data. And Table 2 is like part of people involve from that data.

i want to join both tables. But if i join with Employee ID from table 1 and Recipient ID then data will come wrong for Submitter KPI and if i join Employee ID and Submitter ID then Recipient data will come wrong. As one submitter can give value to more than one Recipient.

Someone suggested me to use flags. But I am not sure how to use. Please suggest.

Re: linking two tables

learnerqlik53 — Fri, 14 Feb 2020 15:27:55 GMT

Adding Excel data and script for reference.

Script:(Not working)

Table1:
LOAD empid,
country,
division,
1 as FLAG
FROM
[Book2.xlsx]
(ooxml, embedded labels, table is Sheet1);

noconcatenate

LOAD [recipient id] as empid,
[recipient id],
[recipient country],
[recipient division],
[submitter id] , ,
[submitter country],
[submitter division]
FROM
[Book2.xlsx]
(ooxml, embedded labels, table is Sheet2);

noconcatenate

Table2:
LOAD empid as empid1,
country,
division,
2 as FLAG
FROM
[Book2.xlsx]
(ooxml, embedded labels, table is Sheet1);
noconcatenate

LOAD [recipient id] ,
[recipient country],
[recipient division],
[submitter id] as empid1,
[submitter id] ,
[submitter country],
[submitter division]
FROM
[Book2.xlsx]
(ooxml, embedded labels, table is Sheet2);

Re: linking two tables

Brett_Bleess — Wed, 11 Mar 2020 18:02:43 GMT

Have a look at the following Design Blog post, may give you an option on how to approach things for your use case, I am also including the base link after that to the are in case you wish to further search for other posts, there are over 700 in this area, mostly how-to related...

https://community.qlik.com/t5/Qlik-Design-Blog/Mapping-as-an-Alternative-to-Joining/ba-p/1473454

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett