topic Re: QlikSense script change content of csv file upon loading in Connectivity & Data Prep

QlikSense script change content of csv file upon loading

SebastienPreau — Wed, 15 Dec 2021 18:27:31 GMT

Hi everyone

We have a very strange issue

We try to import in QlikSense a csv file (around 500.000 fields)

Here are the data as shown by Notepad++

Here is the script

LOAD
NFRN_ID_INT,
NFRN_TYPE,
NFRN_MOTF_CODE,
NFRN_FRNS_CODE,
NFRN_FORC,
NFRN_NIVE,
NFRN_NIVP,
NFRN_CODE,
NFRN_COD1,
NFRN_COD2,
NFRN_COD3,
NFRN_COD4,
NFRN_COD5,
NFRN_LIBE,
NFRN_APPL,
NFRN_DFIN
FROM [lib://Data/Share\FR\REFERENTIELS\PRODUITS\PFRN\NFRN.csv]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

And here is what we get :

I can't figure out how the extra 0 in front of 44 just appeared out of the blue

Any idea ?

PS : tested on versions September 2018 and November 2018

Re: QlikSense script change content of csv file upon loading

marcus_sommer — Fri, 08 Mar 2019 12:58:12 GMT

I think I would add a recno() and isnum(NFRN_COD1) and len(NFRN_COD1) to the load-statement to check the records and the values a bit more closely. Further thoughts go to the codepage and the msq-statement within the fileformat which might not working like expected.

- Marcus

Re: QlikSense script change content of csv file upon loading

Wlad_Masi — Fri, 08 Mar 2019 22:12:56 GMT

Sebastien do you mind sending me a csv with a couple of lines so I can check it here, and what Qlik Sense version you are using there?

Re: QlikSense script change content of csv file upon loading

Vu_Nguyen — Wed, 13 Mar 2019 15:08:30 GMT

Hi Sebastien,
Check if value "044" does appear in field NFRN_COD1 before line 81 in your csv file.
There is a known behavior that when multiple rows having the same numerical value appears in the same column (for example 044, 44, 00044), Qlik engine will take whatever appears first to be the display value.
Check this article for more discussion: https://support.qlik.com/articles/000003759
Hope this helps!

Re: QlikSense script change content of csv file upon loading

SebastienPreau — Thu, 14 Mar 2019 07:48:10 GMT

Hy,

I think you have fund the reason

044 appears upstream of the 47881th record in the same column

44 appears upstream (36717th records) but never in this column before 47881.

If it does solve the issue, at least it explain the pb.

Meanwhile, we seems to have found the work around :

text(NFRN_COD1) AS NFRN_COD1

seems to avoid the pb. But I'm not sure yet that there is no side effect...

Re: QlikSense script change content of csv file upon loading

SebastienPreau — Thu, 14 Mar 2019 07:51:40 GMT

OK, I checked the article after posting, so text() is the solution, my only concern being : does it suppress 0 in 044. So far it seems ok.

Re: QlikSense script change content of csv file upon loading

SebastienPreau — Thu, 14 Mar 2019 07:53:42 GMT

The file was big so i was looking for a solution for file transfer, but as it is resolved, i won't send it.
Thanks anyway for the proposal