Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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.
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.
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
I don't want to unqualify all columns - only NAME.
sum(if(Table2.CCY = Table1.CCY, Table2.Amt2))
Maybe it was slow for you because incorrect. Give it a try.
Regards,
Michael
Why you don't want to unqualify? Why you don't want to Join?
You are losing QV funcionality with these unlinked fields...
Use If function