topic Re: Convert Date to DDMMYYYY from CSV source file in QlikView

Convert Date to DDMMYYYY from CSV source file

Anonymous — Sun, 02 Sep 2018 19:20:03 GMT

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

Re: Convert Date to DDMMYYYY from CSV source file

MarioCenteno — Sun, 02 Sep 2018 19:28:20 GMT

May be try this

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

Re: Convert Date to DDMMYYYY from CSV source file

Anonymous — Sun, 02 Sep 2018 19:30:48 GMT

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

Re: Convert Date to DDMMYYYY from CSV source file

MarioCenteno — Sun, 02 Sep 2018 20:28:43 GMT

Try

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

Re: Convert Date to DDMMYYYY from CSV source file

vishsaggi — Mon, 03 Sep 2018 03:54:10 GMT

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

Re: Convert Date to DDMMYYYY from CSV source file

gawalimegha — Mon, 03 Sep 2018 07:09:01 GMT

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;

Re: Convert Date to DDMMYYYY from CSV source file

manoj217 — Mon, 03 Sep 2018 10:47:13 GMT

use date date# to convert