topic Re: CSV File changing fields (text to number, and decimal separators) in QlikView

CSV File changing fields (text to number, and decimal separators)

Thu, 19 Sep 2013 17:44:05 GMT

Hey guys,

Nowadays I use excel files for a qlikview document, but it has taken quite long time to extract them from my program. We have as an alternative use .csv files, but when I load it, the fields comes as text, and the separator is "."... I want to change this separator to "," (comma as decimal separator), and them make qlikview read this text as numbers.

I tryed to use num#() formula, but it's not working, i think it's due this separator.

So, how do I change that?

Thank you,

Re: CSV File changing fields (text to number, and decimal separators)

fernando_tonial — Thu, 19 Sep 2013 18:00:53 GMT

hi, you can try this in script.

Num(Replace(Filed,'.',',')) as Number

Best Regards.

Tonial

Re: CSV File changing fields (text to number, and decimal separators)

rbecher — Thu, 19 Sep 2013 19:27:51 GMT

Hi Julio,

why num#() is not working? Have you tried with all parameters:

num#(field, '#0.00', '.', ',') as MyNumber

- Ralf

Re: CSV File changing fields (text to number, and decimal separators)

Fri, 20 Sep 2013 13:05:41 GMT

Hey Ralf, i'll try with those, i haven't tried with all parameters...

Re: CSV File changing fields (text to number, and decimal separators)

Fri, 20 Sep 2013 13:41:58 GMT

Ops... virtual memory exceeded... My database das 180,000 lines x 25 columns that needs to be transformed... Is it too much perhaps?

Re: CSV File changing fields (text to number, and decimal separators)

Anonymous — Fri, 20 Sep 2013 13:57:42 GMT

Did you try to manipulate this?

SET MoneyDecimalSep=','; SET MoneyDecimalSep='.';

Alternatively, Add a layer of transformation from xls to QVD before using your data.

Re: CSV File changing fields (text to number, and decimal separators)

Mon, 23 Sep 2013 17:08:14 GMT

I tried that, some values started to appear, but it's like 10% of everything... I don't have a clue why, but it was worth the shot.

Re: CSV File changing fields (text to number, and decimal separators)

Anonymous — Wed, 25 Sep 2013 14:34:52 GMT

OK. Could y ou maybe post examples of some records (or an small extract in XLS and CSV)?

Re: CSV File changing fields (text to number, and decimal separators)

Clever_Anjos — Wed, 25 Sep 2013 14:43:41 GMT

These are 'peanuts' to QlikView.

Could you post a sample of your csv?

Re: CSV File changing fields (text to number, and decimal separators)

Mon, 30 Sep 2013 10:45:30 GMT

I can't upload samples, those are classified =[

Re: CSV File changing fields (text to number, and decimal separators)

rbecher — Mon, 30 Sep 2013 12:06:57 GMT

But you could build a set of similar test data...

Re: CSV File changing fields (text to number, and decimal separators)

Wed, 02 Oct 2013 11:38:49 GMT

sorry guys, i'll hold this topic for some time, have other business to do...