Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbushey1
Creator III
Creator III

Compare two fields

Hi there,

Here is some sample data. What I am trying to do is if the two fields are equal, i.e. field 1 = field 2, I then want to see if any of the values in Compare are also in Field 1. This is id dependant.

Sample:

Id, Field1,

A, 5

A, 10

A, 15

A, 20

B, 2

B, 3

List:

Field2, Compare

20, 5

20, 7

20, 9

20, 11


So in this case I can see 20 in field1 = 20 in field 2, AND 5 in Compare = 5 in Field 1. I would want to return this result and not others. It is important to also identify that 20 was the cause.


Been racking my brain on this all day. Appreciate the help.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Chase,

try this

TempSample:
LOAD * Inline [
Id, Field1
A, 15
A, 10
A, 5
A, 20
B, 2
B, 3
]
;
Sample:
NoConcatenate LOAD * Resident TempSample
Order By Id,Field1;
Drop Table TempSample;

List:
LOAD Field2,Compare,If(Lookup('Field1','Id',F,'Sample') = Compare,1) as Flag;
LOAD *,Lookup('Id','Field1',Field2,'Sample') as F Inline [
Field2, Compare
20, 7
20, 5
20, 9
20, 11
]
;

Regards,

Antonio

View solution in original post

11 Replies
swuehl
MVP
MVP

Maybe something like

INPUT:

LOAD * INLINE [

Id, Field1

A, 5

A, 10

A, 15

A, 20

B, 2

B, 3

];

List:

LOAD Recno() as ListID, * INLINE [

Field2, Compare

20, 5

20, 7

20, 9

20, 11

];

TMP:

NOCONCATENATE LOAD * Resident List;

JOIN

LOAD DISTINCT Id Resident INPUT;

CROSS:

CrossTable (Source, Field1,2)

LOAD ListID, Id, Field2, Compare

RESIDENT TMP;

INNER JOIN (CROSS)

LOAD * Resident INPUT;

INNER JOIN (List)

LOAD If(Count(ListID)=2,ListID) as ListID,  Id

Resident CROSS

GROUP BY ListID, Id;

DROP TABLES TMP, CROSS;

cbushey1
Creator III
Creator III
Author

I wouldnt have thought to use the cross table technique but when I applied this to my actual data set it seemed to error out. It would seem that the cartesian product of joining the ID field is too much for qlik.

Let me elaborate more on my request and maybe you would have other ideas.

Table one represents IDs and their associated codes (field1). Each ID and code (field1) relationship is unique in that it will not be repeated. Table two has two columns, codes (field 2) and compare. 

If the code (field1) from table one matches a code in table 2 (field2) then I need to see if any of the compare codes match a code (field 1) but only where they share the same ID. So in the scenario above, if table 2 had 20, 3 as a record it shouldnt be returned in the final results since 3 is only a code for B and not for A.

Let me know if that is enough context and if it makes sense.

Going to give this some more thought today but would appreciate anything you come up with.

Thanks.

swuehl
MVP
MVP

Where exactly does the error occur in your setup (I assume the script I've posted above does run on your side, right?)?

Can you give some numbers, how many rows each of your tables show and how many distinct values?

Maybe you just missed a proper key for the joins, so if possible, post a small sample set of data or at least your current script.

cbushey1
Creator III
Creator III
Author

It fails here:

join (TMP)

Load Distinct ID RESIDENT INPUT

The error just says user cancelled action but I didnt click on anything.

Input = 600k records

List = 300k

But I expect these numbers to get larger as our data set grows.

antoniotiman
Master III
Master III

Hi Chase,

try this

TempSample:
LOAD * Inline [
Id, Field1
A, 15
A, 10
A, 5
A, 20
B, 2
B, 3
]
;
Sample:
NoConcatenate LOAD * Resident TempSample
Order By Id,Field1;
Drop Table TempSample;

List:
LOAD Field2,Compare,If(Lookup('Field1','Id',F,'Sample') = Compare,1) as Flag;
LOAD *,Lookup('Id','Field1',Field2,'Sample') as F Inline [
Field2, Compare
20, 7
20, 5
20, 9
20, 11
]
;

Regards,

Antonio

swuehl
MVP
MVP

We don't really Need a full outer JOIN here, if this may causes the issue.

Try replacing the TMP table LOAD with



TMP:
 
NOCONCATENATE LOAD * Resident List;
 
LEFT JOIN
 
LOAD DISTINCT Id, Field1 as Field2 Resident INPUT;

pradosh_thakur
Master II
Master II

Hi

Can You Please try this. Attached is the qvw. try changing the value and let me know.

INPUT:

LOAD * INLINE [

Id, Field1

A, 5

A, 10

A, 15

A, 20

B, 2

B, 3

];

inner join

List:

LOAD  * INLINE [

Field2, Compare

20, 5

20, 7

20, 9

20, 11

3, 2

];

inner join

load distinct Id ,Field2 as sw1

Resident INPUT

where Match(Field1,Field2)>0 ;

load Id,sw1 as Field1

Resident INPUT

where Field1=Compare;

drop TABLE INPUT;

regards

Pradosh

Learning never stops.
cbushey1
Creator III
Creator III
Author

Antonio,

Thanks for this. I managed to get this code to work with my dataset. Although the lookup function is slow given my volume of data (25 mins locally, 4 minutes on server) I think it will work. I looked at switching that function out for the map/apply map however I think it loses some of the connections I was trying to create between Id and codes that way.

Thanks again!

pradosh_thakur
Master II
Master II

Did you try this?

Learning never stops.