Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a load script of an csv file with a numeric field (price) where , if there isn't value, there is a single quote.
An example of field in csv:
How I can replace the single quote with nothing?
I tried with
LOAD
PurgeChar(PrezzoListino, chr(39)) as PrezzoListino,
.....
but doesn't work.
I tried also with:
LOAD
replace(PrezzoListino, chr(39),'') as PrezzoListino,
but doesn't work.
Thanks for your help.
Patrizia
LOAD
FieldA,
FieldB,
Replace(FieldB,chr(39),'') as FieldB_new,
FieldC,
Replace(FieldC,chr(39),'') as FieldC_new,
PrezzoListino,
Replace(PrezzoListino,chr(39),'') as PrezzoListino_new,
DataListino,
FieldD,
FieldE,
FieldF
FROM [lib://data/SmpleData.csv]
(txt, utf8, embedded labels, delimiter is ';', no quotes);
it is not working might be it is not single quote.
instead of purgechar() try using keepchar()
try below
KeepChar (PrezzoListino,'0123456789' )
Regards,
Prashant Sangle
Thanks, it works only if there is one field with single quote.
If I have for example following field with 2 field (FieldB and FieldC) with single quote ,this is the situation:
example csv:
FieldA;FieldB;FieldC;PrezzoListino;DataListino;FieldD;FieldE
20191201;';';216732.00;20220615;4V14F9;IB
In Load script:
Load
FieldA,
KeepChar (FieldB,'0123456789' ) as FieldB,
KeepChar (FieldC,'0123456789' ) as FieldC,
KeepChar (PrezzoListino,'0123456789.' ) as PrezzoListino,
DataListino,
FieldD,
FieldC,
....
In data preview on Qlik appear in this way, the values in the subsequent fields are misaligned:
Thank you very much.
Patrizia
is it possible you to share data file?
Maybe there is no single quote as a value else it might be a kind of replacement for nothing respectively a meta-data control char. Means if you want in Excel to make a number to be a string you could change the cell-format or applying any string-function or just adding a single quote before the number. Within the normal Excel view you won't see this extra char - only if you goes directly in the cell it will be displayed.
Therefore you may try to load this field directly without any transformation or checking against a numeric content and adding a default-value if it's not, maybe something like this:
alt(PrezzoListino, null()) as PrezzoListino
Hi Prashant,
I send file in attached.
Thank you very much.
Patrizia
I tried with Replace()
Replace(PrezzoListino,chr(39),'') as PrezzoListino_new,
it is working
LOAD
FieldA,
FieldB,
Replace(FieldB,chr(39),'') as FieldB_new,
FieldC,
Replace(FieldC,chr(39),'') as FieldC_new,
PrezzoListino,
Replace(PrezzoListino,chr(39),'') as PrezzoListino_new,
DataListino,
FieldD,
FieldE,
FieldF
FROM [lib://data/SmpleData.csv]
(txt, utf8, embedded labels, delimiter is ';', no quotes);
I probably thought the problem was just the field "PrezzoListino" when I wrote this post.
Now the field "PrezzoListino" is ok but the problem seems to be the following:
In case there are two consecutive fields with single quote, I have for these records that the values in the subsequent fields are misaligned:
Are you also experiencing the same problem?
Tks
@pattyccia , I already saw that check my second reply after. See highlighted text in bold will do the trick for you