Convert to Date format/Multiple timestamps

[Anonymous]

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

[shubham_singh]

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')

[Anonymous]

Find here data I use. Timestamp column is "EXTRACT_TS"

Thanks again

[surendraj]

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

[vinieme12]

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??

[vinieme12 · Accepted solution]

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

[Anonymous]

I tried all the solutions, I always get only "-2017" in all versions

[Anonymous]

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.

[vinieme12]

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;

[Anonymous]

Thank you Vineeth, your formula works perfect. Big thanks for this help, I struggled for long to get this sorted out.

[vinieme12]

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