Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
gines_rueda
Contributor III
Contributor III

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

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.

12 Replies
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
gines_rueda
Contributor III
Contributor III
Author

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.

gines_rueda
Contributor III
Contributor III
Author

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.