Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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