Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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);
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.
HI,
Check for help in Qlikview help file for Alt(), Date#() and Num#().
Regards,
Jagan.
Please find attached file.
I want to change the two different formats into one format. !
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.
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);
In attached Sample Excel file we are able to see the Date format
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.
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.