Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
SebastienPreau
Contributor III
Contributor III

QlikSense script change content of csv file upon loading

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

one.PNG

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 :

two.PNG

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

 

 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
Vu_Nguyen
Employee
Employee

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!
Vu Nguyen
If a post helps resolve your issue, please mark the answer as correct.

View solution in original post

6 Replies
marcus_sommer

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

Wlad_Masi
Employee
Employee

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?
To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.
Vu_Nguyen
Employee
Employee

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!
Vu Nguyen
If a post helps resolve your issue, please mark the answer as correct.
SebastienPreau
Contributor III
Contributor III
Author

Hy,

I think you have fund the reason

01.png

02.png

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

 

 

 

SebastienPreau
Contributor III
Contributor III
Author

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.

SebastienPreau
Contributor III
Contributor III
Author

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