topic How to clean date format? (URGENT) in QlikView

How to clean date format? (URGENT)

Tue, 11 Oct 2011 06:48:05 GMT

Hi all,

I have an Excel datasource but in the Transaction Date column, it consists of various date format, for example:

7/12/2010

09-08-10

(Blank)

ANy idea how to clean this data?

THanks

How to clean date format? (URGENT)

perumal_41 — Tue, 11 Oct 2011 06:52:18 GMT

Hi,

Use this function Date(field1,'DD/MM/YYYY')

How to clean date format? (URGENT)

SunilChauhan — Tue, 11 Oct 2011 06:52:37 GMT

try like below

load

field1

field2

field3

;

date(Datefiled,'DD/MM/YYYY') as date//in place of dd/mm/yyyy you can take ur format

from a.xls

where Datefiled<>' ' or isnull(datefield)=0;

How to clean date format? (URGENT)

Tue, 11 Oct 2011 07:00:07 GMT

Hi,

Please Use Date Function to convert it into the same format and set a default Date for blank Fields:

If(Len(Date)>0,Date(Datefield,'DD-MM-YYYY'),'01-01-2011') as Datefield

Thanks

Vivek

How to clean date format? (URGENT)

Tue, 11 Oct 2011 07:00:59 GMT

Thanks all for the quick reply

I have used both methods but I still hv dirty data

This is my excel data:

TransactionDate
12/12/2009
12/12/2009
-
12/12/2009
31/7/2010
11-07-10
21-07-10
31-07-10
31-07-10
20/7/2010
20/7/2010

After use date(TransactionDate,'DD/MM/YYYY'), I hv this data:

TransactionDate

12/12/2009

31/7/2010

11-07-10

21-07-10

31-07-10

20/7/2010

Why oh why

How to clean date format? (URGENT)

SunilChauhan — Tue, 11 Oct 2011 07:04:07 GMT

use

ltrim(date(TransactionDate,'DD-MM-YY')) as Date

in ur script.

and see the result

How to clean date format? (URGENT)

Tue, 11 Oct 2011 07:13:53 GMT

Hi all,

Actually it works already but the value changed after Date(TransactionDate, 'DD/MM/YYYY')

From 21-07-10 become 10/07/2021

From 31-07-10 become 10/07/2031

How to clean date format? (URGENT)

SunilChauhan — Tue, 11 Oct 2011 07:18:18 GMT

did u dry my post

can you share the sample

Re: How to clean date format? (URGENT)

Tue, 11 Oct 2011 07:56:16 GMT

Yes, I did try urs

Pls see the attachment

How to clean date format? (URGENT)

SunilChauhan — Tue, 11 Oct 2011 08:27:42 GMT

use below code and then reload the application

TransactionDate:

LOAD

TransactionDate as OriginalTransactionDate,

Date(ltrim(TransactionDate), 'DD-MM-YY') as NewTransactionDate,

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTransactionDate date will show you accurate result

hope this help

Re: How to clean date format? (URGENT)

Tue, 11 Oct 2011 08:29:36 GMT

its the same =(

ive uploaded the source too

Re: How to clean date format? (URGENT)

Tue, 11 Oct 2011 08:40:08 GMT

Hi,

Please see the the attachment,i hope it will solve u r purpose

Thanks

vivek

Re: How to clean date format? (URGENT)

Tue, 11 Oct 2011 08:42:49 GMT

It works! Thanks a bunch!

Buy u coffee next time~

Re: How to clean date format? (URGENT)

its_anandrjs — Tue, 11 Oct 2011 08:50:58 GMT

Hi,

No need to write subfield in place of this you can use replace command because if symbol change you need to write another code by this you can change

Date(Replace(TransactionDate,'-','/'), 'DD-MM-YYYY') as NewTransactionDate2

See the attched sample file solution sheet.

Rgds

Anand

Re: How to clean date format? (URGENT)

Tue, 11 Oct 2011 08:54:07 GMT

PERFECT!