topic Re: csv file load issue: Numeric strings get stored as numbers in App Development

csv file load issue: Numeric strings get stored as numbers

llcolombo — Wed, 20 Sep 2017 10:07:24 GMT

Hi, am using Qlik Sense Desktop 3.2, and I hit an issue loading data from a csv file where a column contains mixed content - can be text or numbers - which should always be treated as text. I did specify in the load statament "load (text(field1)) as field1 from [...]", but if the content of field1 may be interpreted as a number, then it actually gets stored as such, although it apparently will show as text.

This will prevent from having different text values for strings which would represent the same numeric value.

For example, field1 may contain "00022" in a row, and "0022" in another one. Loading these values as text, I would expect the number of leading zeroes to be preserved correctly for each row. Instead, I get all "00022" or all "0022", depending possibly on the order of occurrencies.

For the time being, the only workaround I found out has been to concatenate an alphabetic char to field1 within the load statement, and then deal with the added char in the app in some way.

Would there be any other way to avoid such strings from being stored as numbers?

Thanks,

Lucia

Re: csv file load issue: Numeric strings get stored as numbers

Anonymous — Wed, 20 Sep 2017 15:53:19 GMT

load (text(field1)) should work as you wish it to - it certainly works for me & keeps the leading zero.

I have just done a quick test with a csv file containing :

ID ,Value

1,00022

2,000022

3,0222

4,123

5,0123

6,00123

And this in the script :

LOAD

ID,

text(Value) as Value

FROM [lib://Bill/Data.csv]

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

and it all worked fine with all the leading zeroes retained in the text.

***************

Could you share a sample csv & qvf demonstrating the issue, as there must some other factor in play messing it up.

Re: csv file load issue: Numeric strings get stored as numbers

llcolombo — Wed, 20 Sep 2017 16:56:36 GMT

I guess there must be something triggering the issue, because I actually tried to only load the "messy" column, and that one alone does work as expected.

So am attaching the full csv file, sorry for this. Column "testo1" may contain value "0022 " as well as value "00022".

In the qvf I added a sheet showing that I can only see values "0022" (trimming is ok) instead of "0022" and "00022".

Am not attaching another similar csv file which I normally incrementally load after this one, which leads to only show value "00022" for the whole table (including those values which previously looked like "0022").

Hope this is clear...

thanks

Re: csv file load issue: Numeric strings get stored as numbers

kaushiknsolanki — Wed, 20 Sep 2017 17:27:27 GMT

Hi I can see both the values from the qvf you have shared.

see the screenshot.

what is the output you are looking for?

Regards,

Kaushik Solanki

Re: csv file load issue: Numeric strings get stored as numbers

Anonymous — Wed, 20 Sep 2017 17:40:12 GMT

This seems to load field testo1 in text format with leading zero's

LOAD

text(testo1) as testo1

FROM [lib://test/REGISTRO__2015_maggio-dicembre.CSV]

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

If I add a trim it misbehaves as you describe

LOAD

trim(text(testo1)) as testo1

FROM [lib://test/REGISTRO__2015_maggio-dicembre.CSV]

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

But if do it like this with an outer text wrapper then it behaves ok

Load

text(trim(text(testo1))) as testo1

FROM [lib://test/REGISTRO__2015_maggio-dicembre.CSV]

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

Re: csv file load issue: Numeric strings get stored as numbers

llcolombo — Wed, 20 Sep 2017 20:12:37 GMT

I am expecting 00022 as well, and it looks to me is not in you screenshot.

Il 20 Set 2017 19:28, "Kaushik Solanki" <qcwebmaster@qlikview.com> ha

scritto:

Re: csv file load issue: Numeric strings get stored as numbers

llcolombo — Wed, 20 Sep 2017 20:15:38 GMT

Oh really

Thanks! But I thought trim was a string function and would possibly force a

text behaviour rather than back to mixed... i will text it again...

Il 20 Set 2017 19:40, "Bill Markham" <qcwebmaster@qlikview.com> ha scritto:

Re: csv file load issue: Numeric strings get stored as numbers

swuehl — Wed, 20 Sep 2017 20:46:50 GMT

Right, Trim() is a text function, but I guess the Automatic Number Interpretation kicks in again after the trim has been done.

Would really be nice to have a better control over the Qlik automatisms.

BTW, well spotted,Bill!