topic Re: Date in QlikView

Date

Tue, 15 Mar 2016 01:48:20 GMT

Hi Experts,

I will get data from different sources and my date format is different in each source. How to maintain single format in QV.

Please find attached sample data

Thanks

Re: Date

settu_periasamy — Tue, 15 Mar 2016 02:07:53 GMT

Hi,

Check the Attachment.

Directory;
LOAD [Product ID],
[Product name],
Date(floor(Alt(Timestamp([Start Date]),Date#(SubField([Start Date],' ',1),'DD-MMM-YY')))) as [Start Date],
Date(floor(Alt(Timestamp([End Date]),Date#(SubField([Start Date],' ',1),'DD-MMM-YY')))) as [End Date]
FROM
[Sample Date.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Date

jagan — Tue, 15 Mar 2016 02:39:22 GMT

Hi,

Try like this

Data:

LOAD [Product ID],

[Product name],

Date(Alt(Num#([Start Date]), Date#(Left([Start Date], 9), 'DD-MMM-YY'))) AS StartDate,

Date(Alt(Num#([End Date]), Date#(Left([End Date], 9), 'DD-MMM-YY'))) AS EndDate

FROM

[Sample Date.xlsx]

(ooxml, embedded labels, table is Sheet1);

Hope this helps you.

Regards,

Jagan.

Re: Date

jagan — Tue, 15 Mar 2016 02:40:00 GMT

HI,

Check for help in Qlikview help file for Alt(), Date#() and Num#().

Regards,

Jagan.

Re: Date

Tue, 15 Mar 2016 05:35:54 GMT

Please find attached file.

I want to change the two different formats into one format. !

Re: Date

jagan — Tue, 15 Mar 2016 05:41:11 GMT

HI,

What are this date formats? 61057599:36.7

What is the source for this date field? How to identify what year, month & Date?

Regards,

Jagan.

Re: Date

settu_periasamy — Tue, 15 Mar 2016 06:08:57 GMT

Hi,

Try this..

Directory;
T1:
LOAD [Product ID], 
     [Product name], 
     [Start Date], 
     [End Date],
     Date(MonthStart(DateTime), 'MMM YYYY') AS Month, 
     Date(Floor(DateTime)) AS ServiceStartDate,
     Hour(DateTime) AS ServiceHour;   
LOAD [Product ID], 
     [Product name], 
     Date(Floor(Timestamp(Alt([Start Date],Timestamp#([Start Date],'DD-MMM-YY h.mm.ss[.fff] TT'))))) as [Start Date],
     Date(Floor(Timestamp(Alt([End Date],Timestamp#([End Date],'DD-MMM-YY h.mm.ss[.fff] TT'))))) as [End Date],
     Timestamp(Alt([Start Date],Timestamp#([Start Date],'DD-MMM-YY h.mm.ss[.fff] TT'))) as DateTime    
FROM
[Sample Date.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Date

Tue, 15 Mar 2016 06:09:43 GMT

In attached Sample Excel file we are able to see the Date format

Re: Date

jagan — Wed, 16 Mar 2016 02:12:39 GMT

Hi,

Did you tried this script

Data:

LOAD [Product ID],

[Product name],

Date(Alt(Num#([Start Date]), Date#(Left([Start Date], 9), 'DD-MMM-YY'))) AS StartDate,

Date(Alt(Num#([End Date]), Date#(Left([End Date], 9), 'DD-MMM-YY'))) AS EndDate

FROM

[Sample Date.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

Re: Date

jagan — Wed, 16 Mar 2016 02:40:54 GMT

Hi,

Try like this for Timestamp also

Data:

LOAD [Product ID],

[Product name],

Timestamp(Alt(Num#([Start Date]), Date#(Left([Start Date], 18) & ' ' & Right([Start Date], 2), 'DD-MMM-YY hh.mm.ss TT'))) AS StartDate,

Timestamp(Alt(Num#([End Date]), Date#(Left([End Date], 18) & ' ' & Right([End Date], 2), 'DD-MMM-YY hh.mm.ss TT'))) AS EndDate

FROM

[Sample Date (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

Re: Date

Wed, 16 Mar 2016 03:01:16 GMT

Can you please share app

Re: Date

jagan — Wed, 16 Mar 2016 03:25:43 GMT

HI,

PFA file for solution.

Regards,

Jagan.

Re: Date

Wed, 16 Mar 2016 03:38:12 GMT

Thank You