topic Re: problem with date format,need help please! in QlikView

problem with date format,need help please!

Wed, 13 Aug 2014 07:58:20 GMT

hi,

I have a field coming from a csv file to my script having data in below format

1/14/2015 12:00:00 AM

I want to change this by removing the time stamp from it and make it to result date in

dd/mm/yyyy format.

how can this be done?? please can someone help

sahana

Re: problem with date format,need help please!

avinashelite — Wed, 13 Aug 2014 08:00:33 GMT

Hi Sahana,

Use date(field,'DD/MM/YYYY') as fieldname in the script this will do the job,

Re: problem with date format,need help please!

amit_saini — Wed, 13 Aug 2014 08:03:24 GMT

Sahana,

Try this,

Date(Your_Date _Field, 'DD/MM/YYYY') as Date,

Thanks,

Re: problem with date format,need help please!

Wed, 13 Aug 2014 08:04:39 GMT

hi Avinash

I have tied it but it doesnt work.I have also tried

DATE(FLOOR(Timestamp#(field,'MM/DD/YYYY hh:mm:ss TT') ))

Re: problem with date format,need help please!

avinashelite — Wed, 13 Aug 2014 08:06:20 GMT

can you please post your app.

Re: problem with date format,need help please!

amit_saini — Wed, 13 Aug 2014 08:08:36 GMT

Please see the attachment.

Go to properties--> Number-->Select Date as decide format.

Thanks,
AS

Re: problem with date format,need help please!

amit_saini — Wed, 13 Aug 2014 08:10:35 GMT

Please see the screenshot:

Re: Re: problem with date format,need help please!

Wed, 13 Aug 2014 08:13:58 GMT

In script you can have a look,I have 2 columns.

Expiry,

date(Expiry,'DD/MM/YYYY') as Expiry1 ,

but Expiry1 doesnt work.

Re: problem with date format,need help please!

ashfaq_haseeb — Wed, 13 Aug 2014 08:14:04 GMT

Hi,

Can you attache your sample csv with few dated .

Regards

ASHFAQ

Re: problem with date format,need help please!

Wed, 13 Aug 2014 08:15:55 GMT

hi Amit,

I want to do this conversion in the script level,where I would be using to join with other tables etc.Hence the issue.

Re: problem with date format,need help please!

simenkg — Wed, 13 Aug 2014 08:17:01 GMT

try Date(Date#(subfield(Field,' ',1),'MM/DD/YYYY'),'DD/MM/YYYY')

Re: problem with date format,need help please!

MK_QSL — Wed, 13 Aug 2014 08:20:23 GMT

Date(Floor(Timestamp#([Time Stamp],'M/D/YYYY h:mm:ss TT'))) as Date

Re: problem with date format,need help please!

jagan — Wed, 13 Aug 2014 08:23:50 GMT

Hi,

Try like this

Data:

LOAD

Date(Date#(subfield(DateFieldName,' ',1),'M/D/YYYY'),'DD/MM/YYYY') AS FormattedDate

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: problem with date format,need help please!

Wed, 13 Aug 2014 08:41:28 GMT

thanks a lot all of you for your valuable help.

Re: problem with date format,need help please!

Simen/Jagan

can you please explain the logic here how its working,as I was unable to understand.

Re: problem with date format,need help please!

PrashantSangle — Wed, 13 Aug 2014 08:42:38 GMT

Hi,

Try this,

Date(Date#(Subfield(dateFieldName,' ',1),'MM/DD/YYYY'),DD/MM/YYYY)

Regards

Re: problem with date format,need help please!

jagan — Wed, 13 Aug 2014 08:47:39 GMT

Hi Sahana,

Please find below explanation

subfield(DateFieldName,' ',1) - Returns only Date part

Date#(subfield(DateFieldName,' ',1),'M/D/YYYY') - Converts string date to default date format

Date() - Will format the date into required date format

Hope this helps you.

Regards,

Jagan.

Re: problem with date format,need help please!

simenkg — Wed, 13 Aug 2014 09:13:14 GMT

The subfield(Text,'Delimiter', Field number) splits the Text-string into smaller text strings. It splits them where the delimiter is. In your case the delimiter is a space, hence the ' ' . Since we want the text part that is in front of the space the Field number becomes 1. So subfield('01/14/2014 12:00:00: AM',' ',1) returns '01/14/2014'. Then we use the Date#(Text, Format) function that tells qlikview that it is supposed to read the text as a date with the specified format. So Date#('01/14/2014','MM/DD/YYYY') gives us a date field with the value 14th of january 2014. Then we use the Date(DateField, Format) function. The Date function tells qlikview to change the format of the DateField into the specified Format. So in our case Date(01/14/2014,'DD/MM/YYYY') returns a datefield with format DD/MM/YY or 14/01/2014.

Hope that clears it up.

Regards

SKG