csv file load issue: Numeric strings get stored as numbers

[llcolombo]

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

[Anonymous]

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.

[llcolombo]

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

[kaushiknsolanki]

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

[Anonymous · Accepted solution]

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);

[llcolombo]

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:

[llcolombo]

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...

LC

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

[swuehl]

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!