Convert Date to DDMMYYYY from CSV source file

[Anonymous]

Hi,

I am trying to convert the below date type from a csv file to DDMMYYYY while loading the date into Qlikview. Please help.

Monday, August 6, 2018 1:30 AM

[MarioCenteno]

May be try this

date#(date(Date_FIELD),'DD/MM/YYYY hh:mmtt') as date

[Anonymous]

No, it doesn't work. The date column is blank.

[MarioCenteno]

Try

=date(Date_field,'DD/MM/YYYY hh:mm')

[vishsaggi]

‌ccan you tell me the structure of your csv like is it comma separated ? If yes your date

field will have only Monday and other values will hold in next field separation. If you can screen shot the structure of your csv we can do a work around.

[gawalimegha]

Hello Mohammed Imtiyaz,

Please Find below code that will help to solve this issue.

First I have taken this sample date in text file deleteData.txt : Monday, August 6, 2018 1:30 AM

and Output is  : 8/6/2018

Code:

Main:

LOAD date,

     replace(Replace(date,left(date,Index(date,',',1)),''),',','') as ChkDate    

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Temp:

LOAD * ,

Date(Date#(ChkDate,' MMM DD YYYY hh:mm tt')) as FinalDate

Resident Main;

drop Table Main;

[manoj217]

use date date# to convert