Set Analysis

[#1]

I've linked a list of closing balances to a set of currency exchanges rates using the date of the balance and the MatchInterval of when the conversion rate was valid. I have multiple currencies in both fields so want to return when both currencies match and apply the correct exchange rate.

sum( {$ <BalanceAutoNumber={"=BalanceCurrency=Currency"}>} ClosingBalance*CADMultiply) 

As per an online guide, I have added the field BalanceAutoNumber to the Balances table as my dimension on which to select on. However, I'm only getting results when there I manually select in both fields.

Data looks something like this

Balances

BalanceAutoNumber	DateForCurrency	Currency	ClosingBalance
1	29/10/2015	CAD	500
2	29/10/2015	GBP	100
3	29/10/2015	USD	200

CurrencyRates

StartDate	EndDate	Currency	CADMultiply
01/10/2015	31/10/2015	CAD	1
01/10/2015	31/10/2015	GBP	1.5
01/10/2015	31/10/2015	USD	1.2
01/10/2015	31/10/2015	JPY	1000

The tables are linked using interval match between DateForCurrency, StartDate and EndDate

[sunny_talwar]

Why do you even need set analysis after interval match?

Script:

Balances:

LOAD BalanceAutoNumber,

    DateForCurrency,

    Currency,

    ClosingBalance

FROM

[https://community.qlik.com/thread/235439]

(html, codepage is 1252, embedded labels, table is @1);

CurrencyRates:

LOAD StartDate,

    EndDate,

    Currency,

    CADMultiply

FROM

[https://community.qlik.com/thread/235439]

(html, codepage is 1252, embedded labels, table is @2);

IntervalMatch:

IntervalMatch (DateForCurrency, Currency)

LOAD StartDate,

    EndDate,

    Currency

Resident CurrencyRates;

[#3]

Thanks Sunny, this is what I have done myself to get the answers. However, I wanted to know how to achieve this using Set Analysis so that I can develop those skills.

[sunny_talwar]

I doubt that this can be done using set analysis, may be we can use Aggr() function to get this done, but set analysis might not be possible unless you are willing to cross join the two tables into one.

[#5]

Thanks Sunny, just to clarify in my head - can Set Analysis only be run across a flat data set?

[sunny_talwar]

It can run across different date sets connected properly, but when you are trying to equate two values, that's when it starts to get tricky.