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

[Gerhard]

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;

[David_Friend]

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-...

[Gerhard]

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.

[Kushal_Chawda · Accepted solution]

@Gerhard  It is best suited to do it in expression

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

[Gerhard]

@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'))