topic Expression error using an if/pick match with 2 aggr cases in QlikView

Expression error using an if/pick match with 2 aggr cases

gines_rueda — Fri, 30 May 2014 09:40:22 GMT

Hi,

I have a table with:

Dimension: [MainCcy] (has 2 values 'USD', 'GBP')

Expression:

pick

(

match

(

[MainCcy],

'USD',

'GBP'

SUM(AGGR(SUM([Amount]), [Ccy ISO]) * [Rate To USD]),

SUM(AGGR(SUM([Amount]), [Ccy ISO]) * [Rate To GBP])

)

[Amount] is related with [Ccy ISO] and [Rate To GBP]/[Rate To USD] but no with [MainCcy] in the data model.

If I replace the sum aggr funtions for numbers or justs sums it works, but the AGGR funtions produce something that only the formula for the first MainCcy is being shown the second formula in the column is empty.

Do you know why this is happening?

Could you give me a solution?

Thanks.

Ginés.

Re: Expression error using an if/pick match with 2 aggr cases

swuehl — Fri, 30 May 2014 09:53:36 GMT

Why do you need adavanced aggregation here?

I would try without advanced aggregation aggr() function, but if you need, try with NODISTINCT qualifier:

SUM(AGGR( NODISTINCT SUM([Amount]), [Ccy ISO]) * [Rate To USD]),

SUM(AGGR(NODISTINCT SUM([Amount]), [Ccy ISO]) * [Rate To GBP])

Re: Expression error using an if/pick match with 2 aggr cases

gines_rueda — Fri, 30 May 2014 10:01:53 GMT

NODISTINCT isn't working, escuse me but I haven't explained myself clearly.

I have amounts with serveral CCy's and what I need is the sum of the amounts in 2 consolidated currencies (GBP, USD).

Then I have a Rate to USD and Rate to GBP realted to the currencies to made that conversion.

So I need to multiply each amount for this rate depending on the dimension.

Re: Expression error using an if/pick match with 2 aggr cases

tresB — Fri, 30 May 2014 10:09:34 GMT

Not sure if understood right.

It seems that, you are summing for all, may be it would be simpler :

pick

(

match

(

[MainCcy],

'USD',

'GBP'

SUM([Amount] * [Rate To USD]),

SUM([Amount] * [Rate To GBP])

)

Re: Expression error using an if/pick match with 2 aggr cases

swuehl — Fri, 30 May 2014 10:09:51 GMT

Your dimension MainCcy is not related to the fact table in any way in your data model, right?

So you want to calculate the same sum of amounts in for both dimension values, just multiple with different rates at the end?

I am not sure I understand what you are trying to achieve, could you upload a small sample application with some mock up data and the desired result?

Re: Expression error using an if/pick match with 2 aggr cases

gines_rueda — Fri, 30 May 2014 10:24:47 GMT

Not exactly,

Lets say I have 10 amounts, 3 in EUR, 3 in USD and 4 in GBP.

And I have a relation between ISO Ccy and with [Rate to USD] and [Rate GBP].

So what I whant is the sum of the 10 amounts converted to USD in the first column and converted to GBP in the second.

In order to give you more information I have in the same table [Amount] and [CCy ISO] fields, then I have another table with the Currencies information [CCy ISO], [Rate to USD], and [Rate to GBP].

And MainCCy is not relate with anything.

Re: Expression error using an if/pick match with 2 aggr cases

gines_rueda — Fri, 30 May 2014 10:29:42 GMT

I have tried this but it's getting me so much biger amount that I was expecting.

I think because is not multiplying correctly or doing the sum.

In order to give you more information I have in the same table [Amount] and [CCy ISO] fields, then I have another table with the Currencies information [CCy ISO], [Rate to USD], and [Rate to GBP].

And MainCCy is not relate with anything.

Re: Expression error using an if/pick match with 2 aggr cases

tresB — Fri, 30 May 2014 10:44:55 GMT

Make sure that [CCy ISO] field has unique values in Currency info table.

Re: Expression error using an if/pick match with 2 aggr cases

gines_rueda — Fri, 30 May 2014 11:15:20 GMT

Done, still the same erros in both approaches.

Re: Expression error using an if/pick match with 2 aggr cases

tresB — Fri, 30 May 2014 11:18:22 GMT

Try to create a sample qvw and post, see: Preparing examples for Upload - Reduction and D... | Qlik Community

And explain your expected output there.

Re: Expression error using an if/pick match with 2 aggr cases

PrashantSangle — Fri, 30 May 2014 11:27:56 GMT

Hi,

What is your dimension?

Try this,

If(wildmatch ([MainCcy], 'USD', 'GBP'),

AGGR(SUM([Ccy ISO]) * [Rate To USD]),Dimension_Field),

AGGR(SUM([Ccy ISO]) * [Rate To GBP]),Dimension_Field)

)

You dimension_field must contain unique value.

Regards

Re: Expression error using an if/pick match with 2 aggr cases

gines_rueda — Fri, 30 May 2014 11:49:33 GMT

Ok, I think you have gave me the key.

The Ccy ISO field is into a $Syn table with some other fields, if I change the Ccy ISO to be unique for the amount table and the currency dimension it works.

So that means it's getting duplicates from that automatically created $Syn table.

Do you know a way to keeping that $Syn table, avoid the duplication?

Thanks.

Re: Expression error using an if/pick match with 2 aggr cases

gines_rueda — Mon, 02 Jun 2014 09:37:51 GMT

H Max,

The thing is I have thin Ccy ISO field inside a $Syn table with anothers fields (account ids, and other keys).

So as tresesco said this works but only if I removed the Ccy ISO field from the $Syn table:

pick

(

match

(

[MainCcy],

'USD',

'GBP'

SUM([Amount] * [Rate To USD]),

SUM([Amount] * [Rate To GBP])

)

So basically what I need is an expression that avoids that Ccy ISO virtual duplication inside the $Syn table.

Could you help me with that?

Thanks.

Ginés.