Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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,

11 Replies
fernando_tonial
Valued Contributor

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

hi, you can try this in script.

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

Best Regards.

Tonial

MVP
MVP

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

Hi Julio,

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

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

- Ralf

Not applicable

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

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

Not applicable

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

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

eruditio
Contributor III

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

Did you try to manipulate this?

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

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

Not applicable

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

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.

eruditio
Contributor III

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

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

Employee
Employee

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

These are 'peanuts' to QlikView.

Could you post a sample of your csv?

Not applicable

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

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

Community Browser