Need help to display variety of currencies

[venkatasuresh]

Hi Experts,

I have to display multiple currencies on list box &Pivot table

we have source data in .xlsm file.(PFA)

Input data:

  CURRENCY

£13,500.00
£26,000.00
$1,438,900.00
£15,500.00
£1,035.00
$59,228.29
CAD 321,793.00
$21,173.00
$5,840.00
£429,331.00
£63,284.00
$14,562.40
£89,656.00
SEK 68,793.30
£114,164.00
€32,500.00
£4,963.00
€ 37,000.00
£5,115.60

expected output:(list box/Pivot table):

CURRENCY

£13,500.00
£26,000.00
$1,438,900.00
£15,500.00
£1,035.00
$59,228.29
CAD 321,793.00
$21,173.00
$5,840.00
£429,331.00
£63,284.00
$14,562.40
£89,656.00
SEK 68,793.30
£114,164.00
€32,500.00
£4,963.00
€ 37,000.00
£5,115.60

Thanks,

Suresh V.V

[Digvijay_Singh]

Not sure but here is some work around -

Step 1 - Copy your source file to .txt format ( converted file attached)

Step 2 - Apply some replacing as per below script -

LOAD text(replace(replace(CURRENCY,'｣','£'),'€','€')) as CURRENCY

FROM

[..\..\..\..\..\..\Downloads\Curr.txt]

(txt, codepage is 932, embedded labels, delimiter is ' ', msq);

[venkatasuresh]

Thank you digvijay

But my cause the source data is dynamic, every day 1000+ records going to be add.

in this cause its bit difficult to convert the source file into txt format on daily basis.

Thanks

Suresh V.V

[qv_testing]

If you have Country code/Currency code, we can do,

Like,

IF(CurrencyCode = 'GB' , num(Sum(sales),'£#,##0.00')) as Currenct

You can add  difference countries as well.

[sasiparupudi1]

not sure but you can use this

Alt(Num#(CURRENCY,'£#,##0.00'),Num#(CURRENCY,'$#,##0.00'),Num#(CURRENCY,'CAD#,##0.00'),Num#(CURRENCY,'SEK#,##0.00'),Num#(CURRENCY,'€#,##0.00')) As CurencyValue

[venkatasuresh]

Thank you Sasidhar,

I have implemented your expression but its not producing expected output.

Thanks,

Suresh V.V

[venkatasuresh]

Thank you @raju kannuri

We dont have country code but we have location names ex:

   location        Currency

Charles River	£7,237.00
Covance	$                 82,752.58
Epithelix	€52,900.00

Thanks,

Suresh V.V

[venkatasuresh]

Hi Anil loveisfail‌ do you have any idea how to over come this?

Thanks,

Suresh V.V

[qv_testing]

I feel, if you have location definitely ll get Country/CountryCode some other tables.

Otherwise you have to manually..

[venkatasuresh]

Here the source is XLSM file, we don't have that column

Thanks,

Suresh