Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
trying to import this CSV file,
I can't get the extract_date field to read in Qlikview as a date.
Probably a simple thing but I can't get it to work
thanks!
Dataset,GLN,GTIN,logische_controles_results,manual_audit_results,label_information_results,extract_date |
3M Nederland,"8713258999997","08710400791324","0",,,"25/02/14" |
PurgeChar(Dataset,Chr(34)) as Leverancier,
PurgeChar(GLN,Chr(34)) as GLN_Leverancier,
num(PurgeChar(GTIN,Chr(34))) as GTIN,
PurgeChar(logische_controles_results,Chr(34)) as Logische_Controle_JN,
if(
PurgeChar(logische_controles_results,Chr(34))=0,
'GOED',
if(PurgeChar(logische_controles_results,Chr(34))=1,
'FOUT',
'NIET GECONTROLEERD'
)
)
as Logische_Controle,
PurgeChar(manual_audit_results,Chr(34)) as Handmatige_Controle_JN,
if(
PurgeChar(manual_audit_results,Chr(34))=0,
'GOED',
if(PurgeChar(manual_audit_results,Chr(34))=1,
'FOUT',
'NIET GECONTROLEERD'
)
)
as Handmatige_Controle,
PurgeChar(label_information_results,Chr(34)) as Etiket_Controle_JN,
if(
PurgeChar(label_information_results,Chr(34))=0,
'GOED',
if(PurgeChar(label_information_results,Chr(34))=1,
'FOUT',
'NIET GECONTROLEERD'
)
)
as Etiket_Controle,
PurgeChar(extract_date,Chr(34)) as Datum
FROM
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
Use date#() if your default date format is not the one of the csv file
You may use also TextBetween() to get a text between two separators.
Fabrice
try with
date(date#(replace(extract_date, '"', ''), 'DD/MM/YY')) as extract_date
i think is better if you change
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
with
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Use date#() if your default date format is not the one of the csv file
You may use also TextBetween() to get a text between two separators.
Fabrice