topic How to compare 2 fields using SET ANALYSIS ? in QlikView

How to compare 2 fields using SET ANALYSIS ?

Thu, 23 Aug 2012 20:59:37 GMT

I am actually trying to see how I can compare fields from 2 different tables using SET ANALYSIS.

Table1:

Id Name

100 JOHN

101 JAKE

102 MIKE

104 AMY

Table2:

Name Amt

MIKE 200

AMY 500

BOB 300

In my output I want something like : SUM({$<Table1.Name = Table2.Name>} Table2.Amt) but this is not working.

Can someone please provide the correct syntax for doing this comparison (without using an IF statement, since that’s very memory intensive) ?

Also, please don't suggest the joining of the 2 tables in the script itself, since my example is just a very simplified version of the real one.

Thanks.

Re: How to compare 2 fields using SET ANALYSIS ?

Anonymous — Fri, 24 Aug 2012 00:01:49 GMT

Not quite clear what you're trying to do. Anyway, let me guess...
If it is the same field Name, it is simply
sum(Amt)
If for some reason you keep the field name qualified, Table1.Name and Table2.Name - can't you unqualify them so you have one field Name? Well, if you must keep them qualified, try this:
SUM({$<Table1.Name = P(Table2.Name)>} Table2.Amt)

Regards,
Michael

Re: How to compare 2 fields using SET ANALYSIS ?

jagan — Fri, 24 Aug 2012 06:19:07 GMT

Hi Mayank,

Check attached file for solution

I used following expression as Micheal suggested

=SUM({$<Table1.Name = P(Table2.Name)>} Table2.Amt)

Hope this helps you.

Regards,

Jagan.

Re: How to compare 2 fields using SET ANALYSIS ?

Fri, 24 Aug 2012 12:41:37 GMT

Thanks Jagan. I am expecting the output to only pick those records from Table2 where the Name matches those in Table1, i.e the output should only have 200+500 = 700.

Re: How to compare 2 fields using SET ANALYSIS ?

Fri, 24 Aug 2012 15:13:27 GMT

Thanks all...SUM({$<Table2.Name = P(Table1.Name)>} Table2.Amt) worked for the simple example that I initially gave, however I just realized that my real porblem has a little more complexity. Here is the new example :

Qualify *;

Unqualify Name;

Table1:
Name	CCY	Amt1
JOHN	USD	200
JOHN	CAD	500
JAKE	EUR	800
MIKE	USD	150
AMY	EUR	300
Table2:
Name	CCY	Amt2
JOHN	USD	20
JAKE	CAD	50
JAKE	USD	10
AMY	EUR	30
Desired Output :
Name	CCY	Amt1	Amt2
AMY	EUR	300	30
JAKE	EUR	800	0
JOHN	USD	200	20
JOHN	CAD	500	0
MIKE	USD	150	0

Amt2 is the column where I want to only bring those records from Table2 that match on the CCY.

Output will have same number of records as Table1.

Thanks.

Re: How to compare 2 fields using SET ANALYSIS ?

Anonymous — Fri, 24 Aug 2012 17:07:32 GMT

Th LEFT JOIN will give you the result you illustrated above:

Output:

LOAD * INLINE [
Name,     CCY,     Amt1
JOHN,     USD,     200
JOHN,     CAD,     500
JAKE,     EUR,     800
MIKE,     USD,     150
AMY,     EUR,     300];

LEFT JOIN LOAD * INLINE [
Name,     CCY,     Amt2
JOHN,     USD,     20
JAKE,     CAD,     50
JAKE,     USD,     10
AMY,     EUR,     30;

Regards,
Michael

Re: How to compare 2 fields using SET ANALYSIS ?

Fri, 24 Aug 2012 17:26:49 GMT

I don't want to unqualify all columns - only NAME.

Re: How to compare 2 fields using SET ANALYSIS ?

Anonymous — Fri, 24 Aug 2012 18:45:58 GMT

I think you don't have much choice. Set analysis returns result per whole data set, regardless charts dimensions. So, your choices are limited:

1. Join in script. That is, unqualify CCY field. Or keep your qualified CCY fields and create thier unquilified copy.

2. Use "IF" - but not for Name. You have Name as a link already. This is the correct "IF" expression:

sum(if(Table2.CCY = Table1.CCY, Table2.Amt2))

Maybe it was slow for you because incorrect. Give it a try.

Regards,

Michael

Re: How to compare 2 fields using SET ANALYSIS ?

sebastiandperei — Sat, 25 Aug 2012 22:28:42 GMT

Why you don't want to unqualify? Why you don't want to Join?

You are losing QV funcionality with these unlinked fields...

Re: How to compare 2 fields using SET ANALYSIS ?

Thu, 11 Sep 2014 17:40:39 GMT

Use If function

Re: How to compare 2 fields using SET ANALYSIS ?

Anonymous — Thu, 11 Sep 2014 18:51:02 GMT

First you need to pass the value from Table1.Name into a variable, then you need to use this value in your set analysis expression. Like this example:

=SUM({<Table2.Name = $(=Variable)>} Table2.Amt)

this way is the simplest way to use with Island tables in your data model. I hope this is helpful.

Thanks and regards,

Walid

Re: How to compare 2 fields using SET ANALYSIS ?

Anonymous — Fri, 12 Sep 2014 16:57:33 GMT

Please let me know if this was helpful.

Thanks

Re: How to compare 2 fields using SET ANALYSIS ?

Anonymous — Mon, 07 Mar 2016 15:39:04 GMT

Hi Walid,

If possible to pass multiple values from table 1 (island tables in my data model), do the comparison with table 2 columns, and if it meets certain criteria, then get selected values in table 1? thanks for your help.

Steven