Joining two tables on a not exist condition

[bickyqlik]

I have few excel files.

File 1 having 10 fileds. F1,F2,F3,F4....F10

File 2 having 8 fileds. F1, F12,F13....F18

Did a left join on File 1 to all F1 fields and matching F1 from File 2

Then I read another excel file with a exist condition for F12 from File 2.

Now I have 2 output tables. File 1+ File 2 become table 1 after left join.  Third file add data to table 2 with F12 as exists codition.

Now I need to know F1 field from File 2 is not present in File 1. Basically need to provide a detailed data on missing F1 in File 1 as compared to File 2. I don't know how to create it and linked so that my dashboard can refer it. Best to have an additional filed in table 1 as a flag which can be considered as a record to identify missing F1 in File 1.

Appreciate a quick response on my query

[marcus_sommer]

It might be done with a mapping-table in beforehand which is then included in the file1-load, maybe something like this:

m: mapping load F1, 1 from File2:

File1: load *, applymap('m', F1, 0) as Flag from File1;
left join(File1) load * from File2;
...

- Marcus

[bickyqlik]

Let me give a real example and please advise the code as I am little confused on it.

TABLE 1
EMP	ECODE	STATE	CN
X1	E12	S1	C8
X2	E34	S1	C8
X4545	E345	S1	C67
X4	E345	S1	C78
X5	E345	S2	C76
X6	E67	S2	C7
X1111	E43	S2	C7
X8	E12	S3	C7
X9	E12	S4	C890
X10	E12	S4	C890
X2222	E12	S4	C90
X54	E34	S4	C98
X21	E345	S5	CC98
X20	E345	S2	C98

TABLE 2
EMP	EMP PLAN	EMP PLAN UNIT	REGION
X1	P123	A1	APAC
X2	P123	A12	APAC
X3	P123	A123	APAC
X4	P45	A45	APAC
X5	P567	A56	APAC
X6	P678	A45	EMEA
X7	P678	A908	EMEA
X8	P678	A981	EMEA
X9	P908	A987	APAC
X10	P987	A988	EMEA
X34	P987	A678	APAC
X54	P987	A789	APAC
X21	P987	A980	EMEA
X20	P657	A875	EMEA

Now EMP frm Table 1 need to there in final table. Matching EMP from table 2 also need to be there. if a EMP is there in Table 2 and not there in Table1 then I need to find those entries along with other details to be populated in the same output table or a new table can be created. If its in the same table then an extra field can be created which have some values to distinguish it from others. Would you be able to help me here with a wrapper code?

[bickyqlik]

I have to get all employees from Table 1 with all details and then join with other tables. Only for table 2 need to find out missing EMP's along with details. Getting all the data with left join with Table 1 but when it comes to finding missing EMP from Table 2

[bickyqlik]

Hi,

Any supporting solution to my problem. 

Regards

Bicky

[marcus_sommer]

I assume I would continue with the mentioned approach and extending it with a concatenating of the in File1 missing EMP, maybe in this way:

m: mapping load F1, 1 from File2:

File1: load *, applymap('m', F1, 0) as Flag from File1;
left join(File1) load * from File2;
concatenate(File1) load *, 2 as Flag from File2 where not exists(EMP);
...

Afterwards all EMP will be there - all from File1 because of the left-join-load and all not existing ones from File2 because of the concatenate and the Flag will show which one comes from where - FLAG value =:

• 1 = EMP exists in both sources
• 0 = EMP exists only in File1
• 2 = EMP exists only in File2

- Marcus