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