topic Re: Convert to Date format/Multiple timestamps in QlikView

Convert to Date format/Multiple timestamps

Anonymous — Mon, 06 Feb 2017 08:51:14 GMT

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

Re: Convert to Date format/Multiple timespamps

MK_QSL — Mon, 06 Feb 2017 09:02:52 GMT

What output you are expecting?

Year is not mentioned here... Do you want to link this to current year?

Re: Convert to Date format/Multiple timestamps

Anonymous — Mon, 06 Feb 2017 09:43:34 GMT

Output format desired ideally 'MM/DD/YYYY' where Year is always current year. thanks

Re: Convert to Date format/Multiple timestamps

surendraj — Mon, 06 Feb 2017 10:04:31 GMT

In text box...

Try with

=(Date(date#(SubField('Thu 02-Feb 08:03 PST',' ',2),'DD-MMM'),'DD-MM'))&'-'&Year(today())

Re: Convert to Date format/Multiple timestamps

vinieme12 — Mon, 06 Feb 2017 10:07:11 GMT

Try as below

LOAD *,DATE(Date#(subfield(left(TIMESTAMPIS,len(TIMESTAMPIS)-10),' ',2),'DD-MMM'),'MM/DD/'&year(today())) as DateIS INLINE [

TIMESTAMPIS

Thu 02-Feb 08:03 PST

Thu 02-Feb 16:03 CET

Thu 02-Mar 23:03 SGT

];

Re: Convert to Date format/Multiple timestamps

sdmech81 — Mon, 06 Feb 2017 10:11:53 GMT

HI,

Use String modification function(like Left,right,mid,subfield) inside Date function(Date,Timestamp etc) to sort this out.

Because your date is not in proper format so QLik qill treat it as string so you have to make it as date as said above..Hope it helps

Sachin

Re: Convert to Date format/Multiple timestamps

Anonymous — Mon, 06 Feb 2017 10:17:30 GMT

Will this work when each load would have different content? Its not always exactly 2nd Feb. This was just example

Thanks

Re: Convert to Date format/Multiple timestamps

vinieme12 — Mon, 06 Feb 2017 12:45:44 GMT

Yep it'll work

Change the dates and try

Re: Convert to Date format/Multiple timestamps

Anonymous — Mon, 06 Feb 2017 14:37:43 GMT

Thank you but apparently Im doing something wrong, not sure. I tried renaming "timestampis" to my column header, did not work.

Even still Im loading data for 2nd Feb & 6th Feb but scipt rolls result only for 2nd feb.

Thanks again

Re: Convert to Date format/Multiple timestamps

surendraj — Mon, 06 Feb 2017 16:17:49 GMT

Please load the .xls file for testing..to check wheather the dates are formated correct or not.

Re: Convert to Date format/Multiple timestamps

shubham_singh — Mon, 06 Feb 2017 17:08:31 GMT

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

Re: Convert to Date format/Multiple timestamps

Anonymous — Tue, 07 Feb 2017 09:37:28 GMT

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

Thanks again

Re: Convert to Date format/Multiple timestamps

surendraj — Tue, 07 Feb 2017 09:53:47 GMT

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

Re: Convert to Date format/Multiple timestamps

vinieme12 — Tue, 07 Feb 2017 10:00:25 GMT

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

Re: Convert to Date format/Multiple timestamps

vinieme12 — Tue, 07 Feb 2017 10:04:59 GMT

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

Re: Convert to Date format/Multiple timestamps

Anonymous — Tue, 07 Feb 2017 10:49:36 GMT

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

Re: Convert to Date format/Multiple timestamps

Anonymous — Tue, 07 Feb 2017 10:50:45 GMT

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.

Re: Convert to Date format/Multiple timestamps

vinieme12 — Tue, 07 Feb 2017 10:57:55 GMT

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;

Re: Convert to Date format/Multiple timestamps

Anonymous — Tue, 07 Feb 2017 13:38:34 GMT

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

Re: Convert to Date format/Multiple timestamps

vinieme12 — Tue, 07 Feb 2017 14:06:57 GMT

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