Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Set Analysis

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

BalanceAutoNumberDateForCurrencyCurrencyClosingBalance
129/10/2015CAD500
229/10/2015GBP100
329/10/2015USD200

CurrencyRates

StartDateEndDateCurrencyCADMultiply
01/10/201531/10/2015CAD1
01/10/201531/10/2015GBP1.5
01/10/201531/10/2015USD1.2
01/10/201531/10/2015JPY1000

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

5 Replies

Re: Set Analysis

Why do you even need set analysis after interval match?

Capture.PNG

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;

Not applicable

Re: Set Analysis

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.

Re: Set Analysis

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.

Not applicable

Re: Set Analysis

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

Highlighted

Re: Set Analysis

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.