topic Compare two fields in QlikView

Compare two fields

cbushey1 — Mon, 09 Oct 2017 20:03:10 GMT

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.

Re: Compare two fields

swuehl — Mon, 09 Oct 2017 20:26:26 GMT

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;

Re: Compare two fields

cbushey1 — Tue, 10 Oct 2017 13:42:10 GMT

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.

Re: Compare two fields

swuehl — Tue, 10 Oct 2017 13:53:08 GMT

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.

Re: Compare two fields

cbushey1 — Tue, 10 Oct 2017 14:22:08 GMT

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.

Re: Compare two fields

antoniotiman — Tue, 10 Oct 2017 14:53:16 GMT

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

Re: Compare two fields

swuehl — Tue, 10 Oct 2017 14:53:47 GMT

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;

Re: Compare two fields

pradosh_thakur — Tue, 10 Oct 2017 15:07:06 GMT

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

Re: Compare two fields

cbushey1 — Wed, 11 Oct 2017 15:59:09 GMT

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!

Re: Compare two fields

pradosh_thakur — Wed, 11 Oct 2017 17:11:35 GMT

Did you try this?

Re: Compare two fields

cbushey1 — Thu, 12 Oct 2017 00:28:29 GMT

Hi Pradosh,

Sorry for the delay but I hadn't tried your suggestion as I was working each suggestion in the order they came in. After reviewing your suggestion, it would seem it doesn't quite return the results I was expecting. Using the example code you provided, the results only include Id and field1 or A and 20. The expected results should be ID = A, field 1 = 20, and 'some name' = 5. The point was to see that 5 was also a match in field 1 so that is why it should be returned separately.

Thanks for putting some thought and effort into my problem though, I truly appreciate it.

Re: Compare two fields

pradosh_thakur — Thu, 12 Oct 2017 06:46:44 GMT

i thought you only need ID, Field1. I am happy that you found your solution. Still if you just add "compare" in the last table it will show that too. I have modified that and changes are in red. Please find the attached. Try it out and see if it helps you or optimizes.

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,Compare as "Some field"

Resident INPUT

where Field1=Compare;

drop TABLE INPUT;

regards

Pradosh