To find Not matching/Matching data script

[Nive]

Hello Guys!!

Need a help on below.consider 2 tables 

Tab 1

Load inline [

id,value

1,20;34;67

2,35;45

3,40;54

6,32;87

];

Tab 1

Load inline [

id,value

5,20

2,45

6,40

];

i want to combine(concatenate) the table as 1 single table. also, i want to find if the id's are matched.

Also, if id's are matched then need to check on Value, if the value in 2 table matches any of 1 table value (given as delimiter field) then it is Equal else not equal. 

Expected result:

Tab C:

ID	Flag	Match
1	Only in Tab A	Only in Tab A
2	Matching ID	Equal
3	Only in Tab A	Only in Tab A
6	Matching ID	NotEqual
5	Only in Tab B	Only in Tab B

Thanks in Advance!!

[tresesco]

Like this?

Tab1:
Load  id, value as Value1
   inline [
id,value
1,20;34;67
2,35;45
3,40;54
6,32;87
];
Tab2:
Join
Load *, value as Value2 inline [
id,value
5,20
2,45
6,40
];
Tab3:
Load
	id,
	if(IsNull(Value1), 'Only in Tab2',if(IsNull(Value2), 'Only in Tab1', 'Matching ID')) as Flag,
    if(IsNull(Value1), 'Only in Tab2',if(IsNull(Value2), 'Only in Tab1', If(Index(Value1, Value2), 'Equal', 'NotEqual'))) as Match
Resident Tab1	; 

Drop table Tab1;

[Nive]

Many Thanks Tresesco.. it was working fine with my data. :)..

Can u please help if i have characters into the value field(say. for id=2, value is 35,45,abc) and i wanted to group the whole table with all the numeric terms as numbers and characters as Alphabets.

[tresesco]

One more sample with expected output would help understand the requirement clearly.

[Nive]

Here u go..

Tab A:

1	20,35,abc
2	45,63
3	42,dft

Tab B:

2	45
3	42
1	35

O/p:

Id	Value1	Value2	Type	Flag	Match
1	20		Numeric	Only in Tab A	Only in Tab A
1	35		Numeric	Flag	Only in Tab A
1	abc		Character	Only in Tab A	Only in Tab A
2	45	45	Numeric	Matching ID	Equal
2	63	45	Numeric	Matching ID	Equal
3	42	42	Numeric	Matching ID	Equal
3	dft	42	Character	Matching ID	Equal
7		35	Numeric	Only in Tab B	Only in Tab B

Thanks in advance!!!

[tresesco]

Id 7?

[tresesco · Accepted solution]

May be this?

TabA:
Load 
   id,
   SubField(value,',') as Value1
   Inline[ 
id value
1	20,35,abc
2	45,63
3	42,dft
](delimiter is spaces) ;

Join
TabB:
Load id,
   value as Value2 
   Inline [
id  value
2	45
3	42
7	35
] (delimiter is spaces);

TabC:
Load
    id,
    Value1,
    Value2,
    If(Isnum(Value1&Value2), 'Numeric', 'Characters') as Type,
    if(IsNull(Value1), 'Only in Tab2',if(IsNull(Value2), 'Only in Tab1', 'Matching ID')) as Flag,
    if(IsNull(Value1), 'Only in Tab2',if(IsNull(Value2), 'Only in Tab1', If(Value1=Value2, 'Equal', 'NotEqual'))) as Match
Resident TabA;
Drop tabe TabA;    

[Nive]

Thanks.. but in my data, im missing the mapping.

As table A and Table B are having extra fields. im getting 2 rows

1. Id with 1st table value

2. Id with 2nd table value

Can you use this field name and help:

Tab A with id, value,Name, code,city,state

Tab B with Id,value,Name, address,create date

do we still get the similar o/p as u have arrived  in this type of dataset too?

[Nive]

Hi, for ID:2 and 3, we get 2 Matches (Equal and Not equal) can you help me getting only one match. that is., for id: 2 and 3 value2 is matching with one among the value1 (concatenated field) so it must contain Equal flag only.