-
Re: Convert to Date format/Multiple timespamps
Manish Kachhia Feb 6, 2017 4:02 AM (in response to Martina Kurimska)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
Martina Kurimska Feb 6, 2017 4:43 AM (in response to Martina Kurimska)Output format desired ideally 'MM/DD/YYYY' where Year is always current year. thanks
-
Re: Convert to Date format/Multiple timestamps
surendra j Feb 6, 2017 5:06 AM (in response to Martina Kurimska)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
Vineeth Pujari Feb 6, 2017 5:08 AM (in response to Martina Kurimska)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
Martina Kurimska Feb 6, 2017 5:17 AM (in response to Vineeth Pujari)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
Vineeth Pujari Feb 6, 2017 7:45 AM (in response to Martina Kurimska)Yep it'll work
Change the dates and try
-
Re: Convert to Date format/Multiple timestamps
Martina Kurimska Feb 6, 2017 9:37 AM (in response to Vineeth Pujari)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
-
timestamp_pokus 1.qvw 1.2 MB
-
Re: Convert to Date format/Multiple timestamps
surendra j Feb 6, 2017 11:17 AM (in response to Martina Kurimska)Please load the .xls file for testing..to check wheather the dates are formated correct or not.
-
Re: Convert to Date format/Multiple timestamps
Martina Kurimska Feb 7, 2017 4:37 AM (in response to surendra j)Find here data I use. Timestamp column is "EXTRACT_TS"
Thanks again
-
Re: Convert to Date format/Multiple timestamps
surendra j Feb 7, 2017 4:53 AM (in response to Martina Kurimska)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
Martina Kurimska Feb 7, 2017 5:49 AM (in response to surendra j)I tried all the solutions, I always get only "-2017" in all versions
-
-
Re: Convert to Date format/Multiple timestamps
Vineeth Pujari Feb 7, 2017 5:00 AM (in response to Martina Kurimska)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
Vineeth Pujari Feb 7, 2017 5:04 AM (in response to Martina Kurimska)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
Vineeth Pujari Feb 7, 2017 5:59 AM (in response to Martina Kurimska)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
[C:\Users\vp51284\Downloads\TESTING_Backlog_MQ_consolidated_Latest_Run_20170207.xlsx]
(ooxml, embedded labels, table is Backlog_MQ_consolidated_Latest_)
Where len(trim(EXTRACT_TS))>0;
-
temp.qvw 544.8 K
-
Re: Convert to Date format/Multiple timestamps
Martina Kurimska Feb 7, 2017 8:38 AM (in response to Vineeth Pujari)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
Vineeth Pujari Feb 8, 2017 3:11 AM (in response to Martina Kurimska)
-
-
-
-
-
-
-
-
-
Re: Convert to Date format/Multiple timestamps
Sachin duganavar Feb 6, 2017 5:11 AM (in response to Martina Kurimska)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
Shubham Singh Feb 6, 2017 12:08 PM (in response to Martina Kurimska)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
Martina Kurimska Feb 7, 2017 5:50 AM (in response to Shubham Singh )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.
-