Mathced and Mismatched records

[pala_jagadeesh]

Hi I have three tables

Table1:                                               Table2:       

Empname	MSteamIDS
john,pal	AB;AC;AD
jey,pals	BC;BD;BE
hem,haa	CA;CB;CD
jun,joj	FD;FC;FG
ane,mur	SD
zne,rut	SE
app,bis	QW

Table2:

Firstname	lastname	Email
john	pal	AB
jey	pals	BC
ane	mur	SD
bne	nut	WE
cne	but	RT
kne	put	YU

table3:

firstname	lastname	Mail
john	pal	AB
jey	pals	BC
zne	rut	SE
bne	nut	WE
yup	tap	UY
jack	kate	JH
jun	joj	FC

I want ouput like below

mails	MsteamIDs	Email	Mail
AB;AC;AD	Yes	Yes	Yes
BC;BD;BE	Yes	yes	Yes
CA;CB;CD	Yes	No	No
FD;FC;FG	Yes	No	Yes
SD	Yes	Yes	No
SE	Yes	No	Yes
QW	Yes	No	No
WE	NO	Yes	Yes
RT	NO	Yes	No
YU	NO	Yes	No
UY	NO	No	Yes
JH	NO	No	Yes

i want to create two buttons like below when i clik on mathced_button out put come like below

mails	MsteamIDs	Email	Mail
AB;AC;AD	Yes	Yes	Yes
BC;BD;BE	Yes	yes	Yes

 i want to create two buttons like below when i clik on nonmathced_button out put come like below

mails	MsteamIDs	Email	Mail
CA;CB;CD	Yes	No	No
FD;FC;FG	Yes	No	Yes
SD	Yes	Yes	No
SE	Yes	No	Yes
QW	Yes	No	No
WE	NO	Yes	Yes
RT	NO	Yes	No
YU	NO	Yes	No
UY	NO	No	Yes
JH	NO	No	Yes

counts like below

Matched_count	2
Nonmatched_count	10

[justISO · Accepted solution]

Hi, so 'trickiest' part is to merge your 3 tables as you want. Not so elegant solution, but in load script this could be done with script below, where I create your 3 sample table (table1, table2, table3. Probably you will load them from some file or database so this part should be adjusted accordingly), I split some combined 'MSteamIDS' to see what unique values are here in split_mails table. At last I create unique value list again and marking yes/no in which table these values were. Additionally I create indicator Matched/Unmatched for your 'buttons'. This indicator can easily be used as filter instead of your 'buttons'. Last table is just match_ind dimension and count(mails) as measure.

table1:
load * inline [
Empname |	MSteamIDS
john,pal |	AB;AC;AD
jey,pals |	BC;BD;BE
hem,haa |	CA;CB;CD
jun,joj |	FD;FC;FG
ane,mur |	SD
zne,rut |	SE
app,bis |	QW
] (delimiter is '|');

table2:
load * inline [
Firstname, 	lastname, 	Email
john, 	pal, 	AB
jey, 	pals, 	BC
ane,	mur, 	SD
bne, 	nut, 	WE
cne, 	but, 	RT
kne, 	put, 	YU
];

table3:
load * inline [
firstname, 	lastname, 	Mail
john, 	pal, 	AB
jey, 	pals, 	BC
zne, 	rut, 	SE
bne, 	nut, 	WE
yup, 	tap, 	UY
jack, 	kate, 	JH
jun, 	joj, 	FC
];

split_mails:
  LOAD
    MSteamIDS as mails,
    1 as MsteamIDs,
    SubField(MSteamIDS,';') as splited_MSteamIds
RESIDENT table1;

JOIN
  LOAD
    Email as splited_MSteamIds,
    1 as Email
RESIDENT table2;

JOIN
  LOAD
    Mail as splited_MSteamIds,
    1 as Mail
RESIDENT table3;

DROP TABLES table1, table2, table3;

main:
  LOAD DISTINCT
    if(not isnull(mails), mails, splited_MSteamIds) as mails,
    if(sum(MsteamIDs)>0, 'Yes','No') as MsteamIDs,
    if(sum(Email)>0, 'Yes','No') as Email,
    if(sum(Mail)>0, 'Yes','No') as Mail,
    if(sum(MsteamIDs)>0 and sum(Email)>0 and sum(Mail)>0, 'Matched', 'Unmatched') as match_ind
RESIDENT split_mails
GROUP BY if(not isnull(mails), mails, splited_MSteamIds);

DROP TABLE split_mails;

[pala_jagadeesh]

WOW,

Hi @justISO 

Thank you so much, its working fine.