Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello ,
I'm have a field in this format 1/1/2012 7:15:54PM ie MM//DD/YYY HH:MM:SS .i"m using Qlikview 11 version.
I want to separate date and time component . How can this be done.
Attaching an example qvw
Thanks
B
Use the floor and frac functions to split the datetime in date and time parts:
Date_field:
LOAD
Date_time,
floor(Date_time) as Date,
frac(Date_time) as Time
FROM
\\Falconetwork\dsteam$\_Swarup\Camp\date_time.xls
(biff, embedded labels, table is Sheet1$);
Use the floor and frac functions to split the datetime in date and time parts:
Date_field:
LOAD
Date_time,
floor(Date_time) as Date,
frac(Date_time) as Time
FROM
\\Falconetwork\dsteam$\_Swarup\Camp\date_time.xls
(biff, embedded labels, table is Sheet1$);
Hi Prabhu,
Date_field:
LOAD Date_Time,
Date(Timestamp#(@1,'MM/DD/YYYY hh:mm:ss TT'),'DD-MM-YYYY') AS Date,
Time(Timestamp#(@1,'MM/DD/YYYY hh:mm:ss TT'),'DD-MM-YYYY') AS Time
From
\\Falconetwork\dsteam$\_Swarup\Camp\date_time.xls
(biff, embedded labels, table is Sheet1$);
above script is usefull for seperating date and time.
Regards
Thank you guys
B
Hi Prabhu,
Above mentioned floor and frac functions also useful but in frent of those script add date for date and time function for time.
Date_field:
LOAD
Date_time,
DATE(floor(Date_time),'DD-MM-YYYY') as Date,
time(frac(Date_time),'hh:mm:ss') as Time
FROM \\Falconetwork\dsteam$\_Swarup\Camp\date_time.xls(biff, embedded labels, table is Sheet1$);ds
Rgards