How to compare 2 fields using SET ANALYSIS ?

[#1]

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.

[Anonymous]

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

[jagan]

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.

[#4]

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.

[#5]

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.

[Anonymous]

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

[#7]

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

[Anonymous]

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

[sebastiandperei]

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

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

[#10]

Use If function