Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Hope you are doing great!
Can any one please explain how to combine multiple fields into one field?
Please find the attached excel example data.
from timestamp field i need only date and need to combine in to one field.
Thanks for advance.
hi
are you expecting the result of this script?
note : your last column is camptured date in the sheet :d :d :d
LOAD DISTINCT StoreID,
ManagerName,final_date
WHERE LEN(TRIM(final_date))>0
;
load StoreID,
ManagerName,date#(SubField(CapturedDate1_date &','& CapturedDate2_date & ',' & CampturedDate3_date,','),'MM/DD/YYYY') as final_date
;
LOAD StoreID,
ManagerName,
date(floor(TIMESTAMP#(CapturedDate1,'YYYY-MM-DD hh:mm:ss')))as CapturedDate1_date,
date(floor(TIMESTAMP#(CapturedDate2,'YYYY-MM-DD hh:mm:ss'))) as CapturedDate2_date,
date(floor(TIMESTAMP#(CampturedDate3,'YYYY-MM-DD hh:mm:ss'))) as CampturedDate3_date,
CapturedDate1,
CapturedDate2,
CampturedDate3
FROM
Sampledata.xlsx
(ooxml, embedded labels, table is Sheet1);
no errors but there is no data in fields, finalDate, Date1,Date2,Date3
Thanks for your help Pradhosh,
Its working.
can i see the dates from starting. like if the date is started from July. It should show like this and format also.
need to see ' . ' (dot) Instead of '-' . How to remove that space in DO Number listbox?
02.05.2017
10.05.2017
06.06.2017 etc.
LOAD DISTINCT StoreID,
ManagerName,final_date
WHERE LEN(TRIM(final_date))>0
;
load StoreID,
ManagerName,date(date#(SubField(CapturedDate1_date &','& CapturedDate2_date & ',' & CampturedDate3_date,','),'MM/DD/YYYY').'DD.MM.YYYY') as final_date
;
LOAD StoreID,
ManagerName,
date(floor(TIMESTAMP#(CapturedDate1,'YYYY-MM-DD hh:mm:ss')))as CapturedDate1_date,
date(floor(TIMESTAMP#(CapturedDate2,'YYYY-MM-DD hh:mm:ss'))) as CapturedDate2_date,
date(floor(TIMESTAMP#(CampturedDate3,'YYYY-MM-DD hh:mm:ss'))) as CampturedDate3_date,
CapturedDate1,
CapturedDate2,
CampturedDate3
FROM
Sampledata.xlsx
(ooxml, embedded labels, table is Sheet1);
IN THE LIST BOX SORT IT BY final_date to see it sorted .
for DO Number use if(len(trim([ DO Number]))>0,[ DO Number]) as expression in list box
script is not reloading, strucked at initially only. I have lot of fields in my table.
load
[Trip Id],
[Vehicle Number],
Status,
[Movement Type],
[Do Number],
Plant1,
[Carrier Name],
[Carrier Code],
[Stage1 Driver Id],
[Stage1First Name],
[Stage1Last Name],
[Stage1Truck Compliance],
[Stage1Driver Compliance],
[Stage1User Id],
[Stage1Device Id],
[Stage1Approved/Rejected By],
[Stage1Approved/Rejected Date],
[Stage2Driver Id],
[Stage2First Name],
[Stage2Last Name],
[Stage2Truck Compliance],
[Stage2Driver Compliance],
[Stage2User Id],
[Stage2Device Id],
[Stage2Approved/Rejected By],
[Stage2Approved/Rejected Date],
[Stage3Driver Id],
[Stage3First Name],
[Stage3Last Name],
[Stage3Truck Compliance],
[Stage3Driver Compliance],
[Stage3User Id],
[Stage3Device Id],
[Stage3Approved/Rejected By],
[Stage3Approved/Rejected Date],
date((SubField(CapturedDate1_date &','& CapturedDate2_date & ',' & CampturedDate3_date,','),'MM/DD/YYYY'),'DD.MM.YYYY')as final_date
;
You are not following the way i have described . Please follow and it shall work.
Thanks for your time and help Pradosh,
Its working now