Compare two fields

[cbushey1]

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.

[swuehl]

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]

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]

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]

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 · Accepted solution]

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]

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]

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

[cbushey1]

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]

Did you try this?