topic Re: Qlik Sense multiple currencies for same item in script - how to correctly format in App Development

Qlik Sense multiple currencies for same item in script - how to correctly format

Gerhard — Tue, 12 Nov 2024 14:15:04 GMT

Hi,

My data contains items with multiple currencies, I want to display the correct currency symbol in my tables. I want to do the formatting in the script. i.e using num()

Below is my script. I tried an if statement as below but the problem once it finds EUR for example it formats all values with the EUR format which makes sense.

Current output - the last row should be formatted with $

Any ideas?

I also tried mapping the format in a field to my script table, to then use the value in the num() function. But the format option must be a string and I could not get it to work.

t:
load * Inline [
Item,Code,Value
1,USD,50
1,EUR,40
2,EUR,100
2,USD,100
];

t2:
load *,if(Code='EUR',num("Value",'€#,##0.00'),if(Code='USD',num("Value",'$#,##0.00'),Value)) as "New Value";
Load Item,Code,Value

resident t;

drop table t;

Re: Qlik Sense multiple currencies for same item in script - how to correctly format

David_Friend — Tue, 12 Nov 2024 14:34:31 GMT

It looks like someone may have figured this out in QlikView, does this help:

https://community.qlik.com/t5/QlikView-App-Dev/How-show-different-currencies-if-I-only-have-my-data-in-one/td-p/1774244

Re: Qlik Sense multiple currencies for same item in script - how to correctly format

Gerhard — Tue, 12 Nov 2024 15:03:28 GMT

Thanks but this is something very different. They are basically creating the currency code, I already have that - what I struggle with is formatting the value with the appropriate currency symbol. I'll only have one value field in my chart/table.

Re: Qlik Sense multiple currencies for same item in script - how to correctly format

Kushal_Chawda — Tue, 12 Nov 2024 15:19:15 GMT

@Gerhard It is best suited to do it in expression

=num(Sum(Value),pick(match(Code,'USD','EUR'),'$#,##0.00','€#,##0.00'))

Re: Qlik Sense multiple currencies for same item in script - how to correctly format

Gerhard — Tue, 12 Nov 2024 16:19:59 GMT

@Kushal_Chawda thanks this is a great idea! Even though my value is not an expression but rather a dimension. I've written it as an master measure as below. My table is a dynamic table where the user can select what dimensions/measures to show, what I'll do is force it show the code when the value is made visible thus both will be displayed and it will work. Thanks for your help.

num([New Value],pick(match(Code,'USD','EUR'),'$#,##0.00','€#,##0.00'))