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
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
What output you are expecting?
Year is not mentioned here... Do you want to link this to current year?
Output format desired ideally 'MM/DD/YYYY' where Year is always current year. thanks
In text box...
Try with
=(Date(date#(SubField('Thu 02-Feb 08:03 PST',' ',2),'DD-MMM'),'DD-MM'))&'-'&Year(today())
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
];
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
Will this work when each load would have different content? Its not always exactly 2nd Feb. This was just example
Thanks
Yep it'll work
Change the dates and try
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
Please load the .xls file for testing..to check wheather the dates are formated correct or not.