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.

[MarcoWedel]

Hi,

can you post some sample xls?

thanks

regards

Marco

[bbi_mba_76]

Hi,

maybe you could use the alt function to specify different number formats.

[maxgro]

maybe

if(IsNum(val), val ,  replace(text(val), '.', ',')) as val,

[datanibbler]

Hi all,

thanks for the answers!

Unfortunately, posting this xls is not an option as it's sensible data about our costs. I don't know what the issue is, so if I were to construct a fake sample, I might not get it reproduced and that would be all right ...

Strangely, a series of numbers that seem to be all right (one value per month, formatted as currency in Excel) come from the same file as the number that is different (though it is seemingly formatted in the same way.

That text() function seems worth a shot, though. That's the counterpart of the num#() function.

I'll have another look and I'll keep you posted if I find out anything.

Best regards,

DataNibbler

[datanibbler]

Aha - I have found out something:

The two files (for two plants) are on different servers which might run different operating systems or have different settings:

=> From the one file, all the values come in numeric format, with the ',' as decimal separator (some come with the '.'

     as thousand_separator, some without, but that should not be an issue)

<=> From the other, values seem to come with the '.' as decimal separator - most, anyway

       <=> one figure from that same file comes with the ',' as decimal_separator again

I am at a loss here - how can I format the figures from the second file to all get the ',' as decimal_separator?

[jagan]

Hi,

Try to format using Alt(), Num(), Num#() like this

=Alt(Num#(value, 'Format1'), Num#(value, 'Format2'),Num#(value, 'Format3'),Num#(value, 'Format4'),............Num#(value, 'FormatN'),)

=Alt(Num#(value, '#,###'), Num#(value, '#,###.###'),Num#(value, '###.###'),Num#(value, '$#,###.##'),............Num#(value, 'FormatN'),)

Hope this helps you.

Regards,

Jagan.

[datanibbler]

Hi jagan,

building a num#() function to recognize different possible formats seems like a very promising idea.

Part of the problem is probably that I don't really understand the different format_codes you use as a parameter within the num#() function. The integrated help is not very helpful in this case.

Is there some listing around where I can see the different possible format_codes and what they mean?

Thanks a lot!

[jagan]

Hi,

Sample for money format

SET MoneyFormat='$#,##0.00;-$#,##0.00';

From Qlikview help file.

num#(expression [ , format-code[ , decimal-sep [ , thousands-sep] ] ])

The num# function evaluates the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the default number format set by script variables or in the operating system is used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Number format	# ##0,#	#,##0.#

num#( A, '#' ) where A=35,648.375 returns:

	Setting 1	Setting 2
String	35,648.375	35648.375
Number	-	35648.375

num#( A, '#.#', '.' , ',') where A=35,648.375 returns:

	Setting 1	Setting 2
String	35,648.375	35,648.375
Number	35648.375	35648.375

num#( A, '#.#',',','.' ) where A=35648.375 returns:

	Setting 1	Setting 2
String	35648.375	35648.375
Number	35648375	35648375

num#( A, 'abc#,#' ) where A=abc123,4 returns:

	Setting 1	Setting 2
String	abc123,4	abc123,4
Number	123.4	1234

Regards,

Jagan.

[datanibbler]

Well - as I said, the integrated help doesn't help me in this case. I just don't understand how to use it.

It still doesn't work.

My script has the "german format" set -

>> SET MoneyFormat='#.##0,00 €;-#.##0,00 €'; <<

It seems like the figures coming from the other plant have the ',' as thousand_separator and the '.' as decimal_separator - the figures coming from this plant are formatted just the other way round.

=> I have to reformat the figures coming from the other plant to "german format" (the ',' as thousand_separator and the '.' as decimal_separator).

I tried the alternative way of turning it into text, replacing the '.' by a ',' and turning it into a numeric value again, but that didn't work - the two figures I have for one month then look the same, but they are still not summed up properly.