Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Nive
Partner
Partner

To find Not matching/Matching data script

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:

IDFlagMatch
1Only in Tab AOnly in Tab A
2Matching IDEqual
3Only in Tab AOnly in Tab A
6Matching IDNotEqual
5Only in Tab BOnly in Tab B

 

Thanks in Advance!!

Labels (1)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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;    

Capture.PNG

View solution in original post

8 Replies
tresesco
MVP
MVP

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;

Capture.PNG

Nive
Partner
Partner
Author

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
MVP
MVP

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

Here u go..

Tab A:

120,35,abc
245,63
342,dft

 

Tab B:

245
342
135

 

O/p:

IdValue1Value2TypeFlagMatch
120 NumericOnly in Tab AOnly in Tab A
135 NumericFlagOnly in Tab A
1abc CharacterOnly in Tab AOnly in Tab A
24545NumericMatching IDEqual
26345NumericMatching IDEqual
34242NumericMatching IDEqual
3dft42CharacterMatching IDEqual
7 35NumericOnly in Tab BOnly in Tab B

 

Thanks in advance!!!

tresesco
MVP
MVP

Id 7?
tresesco
MVP
MVP

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;    

Capture.PNG

Nive
Partner
Partner
Author

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
Partner
Partner
Author

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.