Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI all,
anyone can help to convert below format to standard date format? I used several approaches but I was not successfull. Thanks
The input format is here:
Thu 02-Feb 08:03 PST | Thu 02-Feb 16:03 CET | Thu 02-Feb 23:03 SGT
Thank you for help
Martina
Looks like you have dates of different time zones, do you want to convert them to a single timezone or do you want to keep timezone.
If timezone is ignored, this should work.
Date#(mid(5,6)&'-'&Year(today()),'DD-MMM-YYYY')
Find here data I use. Timestamp column is "EXTRACT_TS"
Thanks again
Thanks for data..!!
I tried it with my expression.
Tested in text box.
=(Date(date#(SubField(EXTRACT_TS,' ',2),'DD-MMM'),'DD-MM'))&'-'&Year(today())
When I selected 06-feb..text box is also showing 06-02-2017.
and your data has different time zones.so you need to convert it into one perticular tim zone (or) break your field into 3 pieces for PST,CET,SGT..by using subfield function.
SubField(EXTRACT_TS,'|',1) as PST_zone,
SubField(EXTRACT_TS,'|',2) as CET_zone,
SubField(EXTRACT_TS,'|',3) as SGT_zone
so do you want the output like
dd-mm-yyyy | dd-mm-yyyy | dd-mm-yyyy or should one row be split into three rows??
try like
LOAD EXTRACT_TS,
DATE(Date#(mid(trim(subfield(EXTRACT_TS,'|',1)),5,6),'DD-MMM'),'DD-MM')&'-'&Year(today()) as Extract_DATE,
Business_Group,
Business_Sub_Group, ..........your usual load
I tried all the solutions, I always get only "-2017" in all versions
I wish to reach CET simple date format "MM-DD-current YYYY since its not in initial data. Sounds simple but still I tried all approaches here, but Im not yet successfull.
Check the attached, works for me!
LOAD EXTRACT_TS,
DATE(Date#(mid(trim(subfield(EXTRACT_TS,'|',1)),5,6),'DD-MMM'),'DD-MM')&'-'&Year(today()) as Extract_DATE,
Business_Group,
Business_Sub_Group,
GBU,
PL,
Platform,
Model,
Product,
Material,
Material_Description,
Plant,
Sales_document,
SO_Item,
Order_quantity_EA,
Partial_qty_EA
FROM
(ooxml, embedded labels, table is Backlog_MQ_consolidated_Latest_)
Where len(trim(EXTRACT_TS))>0;
Thank you Vineeth, your formula works perfect. Big thanks for this help, I struggled for long to get this sorted out.
Glad to help
axinka853 Can you mark a response as correct and close the thread please
Qlik Community Tip: Marking Replies as Correct or Helpful