data go wrong in dual()

[#1]

for the table that has money variable that has different currencies for each value.

I loaded table with the sentences below.

LOAD

     money,//text that contains currency symbol and ,(comma)

     PurgeChar(money,'0123456789,') as Currency_Symbol,

     Dual(money, Num(PurgeChar(money,'$€£RCHF₹¥₩kr'))) as money_dual

Resident Temp;

The correct value from Temp is money text column, and extract Currency_Symbol correctly, but in money_dual column some of the value go wrong...(except dollar sign$)

[petter]

That is the limitation of dual-values in Qlik. It is designed so one particular numeric value matches one and only one text-value. So if you have the same amount in different currencies it will be the currency that comes first in your load that "wins". Then the text value associated with that particular numeric value (which have no notion of currency at all) that decides which currency will be shown.

So for your purpose you just have to store currency in a separate field in the load and either concatenate the two fields in the charts you are making or display them in separate columns.

You can however use the Dual()-function in your charts by joining the currency symbol and the amount like this:

=Dual( Curr&Val, Val )

It will be right adjusted and act like a number in all calculations....

[#3]

thank you for your answer!!!

I did what you suggested, but it still gives the same result....

Dual(purgechar(money,'0123456789,')&Num(keepchar(money,'0123456789')), Num(keepchar(money,'0123456789'))) as money_dual

above is my code for money_dual

but it's result is just same as picture above...

[petter]

You will have to have two separate fields. You can't use Dual() to achieve what you want in the load script and subsequently in the data model.

You can in your UI use the Dual to get the display correctly and avoid the problems you pointed out. I have a full test-application to prove that it works if you want to investigate it?

[#5]

I would really appreciate it if you send it to me!!

[petter · Accepted solution]

I have attached a QVF and here is a couple of screenshots from the app:

[#7]

I really appreciate your help!!