How to adapt format of numbers (currency) coming from Excel?

[datanibbler]

Hi,

I have two numbers (both representing amounts) coming from Excel.

What I want to do is to aggregate those because they are for the same month in the same year.

<=> For some reason, one of these numbers comes out with a '.' for a decimal_separator and one with a ','.

(in the script, it is set to german format, the '.' being the thousand_separator and the ',' being the decimal_separator)

=> Why does that other number still come out with a '.' for decimal_separator?

(that obviously keeps me from aggregating the two - I've tried time and again, but it won't work)

Can anybody help me here?

Thanks a lot!

Best regards,

DataNibbler

P.S.: I am already testing with the num# function - but unfortunately, the help_file is not particularly helpful here.

[jagan]

Hi,

Can you post some sample numbers you want to convert and what is your expected format, so that I will give you the expression.

Hope this helps you.

Regards,

Jagan.

[datanibbler]

Hi,

thanks!

I have tried with repeating that SET command and just turning it around for those figures. That way, the figures look the same in a listbox, with a ',' as decimal_separator - but they are still not added up, so something must be different.

Strangely, some figures from the file result like

>> 10000,50  << (ten_thousand_(Euro)_fifty)

and some result like

>> 10000.50 << (ten_thousand_(Euro)_fifty)

(though they come from the same Excel_file)

=> I want them all to be formatted in "german numeric format", like

>> 10.000,50 <<

I suppose when the figures all have the same numeric format, I can also aggregate them.

Thanks a lot!

[jonathandienst]

Hi

Assuming your field is called Amount, and that the amounts can come in as one of:

     12.345,99

     12,345.99

     12345,99

     12345.99

Then use this:

Num(Alt(Num#(Amount, '#.##0,00', ',', '.'), Num#(Amount, '#,##0.00', '.', ','), Num#(Amount, '#0,00', ',', '.'), Num#(Amount, '##0.00', '.', ',')))

The outer Num will ensure that the final results are all formatted using your model's default numeric format. Now just change Amount to the correct field name.

HTH

Jonathan

[jonathandienst · Accepted solution]

Or use this:

Num(Alt(Num#(Amount, '#.##0,00', ',', '.'), Num#(Amount, '#,##0.00', '.', ','), Num#(Amount, '#0,00', ',', '.'), Num#(Amount, '##0.00', '.', ',')), '#.##0,0', ',', '.')

to explicitly format the numbers in German format.

[datanibbler]

Hi jonathan,

I think this will help me.

So the parameters inside the num#() function all have to be there and match, right? (the format_code is the same I would use in a num() function and the decimal_separator and thousand_separator I specify there have to match that)?

And I have to specify inside the num#() function whether or not there is a "thousand"_digit, right?

[datanibbler]

Hi,

I don't know exactly, I think I did this before to no avail -

but now it works.

I reformat the figures that way and I can aggregate them.

That helps me a great step forward.

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi,

now I have another issue:

I have just re-formatted the figures from 2013 in Excel, they seem to have been in different formats.

My script_settings are for "german numeric format", with the ',' as decimal_separator and the '.' as thousand_separator. That is on the "Main" tab of tthe script.

<=> Still, when I view the figures in a listbox, they have the '.' as decimal_separator!?

Can you tell me why this happens?

Best regards,

DataNibbler

[datanibbler]

It seems that some of the figures are misinterpreted by QlikView.

When I load the field such as it is, the figures all have the '.' as decimal_separator.

When I use the method proposed by Jonathan to re-format them (in the LOAD)

>> num(num#([Claimkosten gesamt], '#,##0.00', '.', ','), '#.##0,00') as [Claimkosten gesamt] <<

some of the figures do change their format - and some don't.

That is annoying because in the underlying Excel_file, the figures are all formatted the same way.

Can you make a rhyme of this?

[jonathandienst]

Perhaps some of them are read as text values (perhaps they don't match the format in the Num#() or they have hidden characters...).

Can you post an example excel sheet to illustrate the problem. Keep 20-30 rows of the problem column with amounts that format both ways, and delete the other columns.

Jonathan