How to combine three fields in to one field from same table

[rkpatelqlikview]

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.

[pradosh_thakur]

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

[rkpatelqlikview]

no errors but there is no data in fields, finalDate, Date1,Date2,Date3

[rkpatelqlikview]

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.

[pradosh_thakur]

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

[rkpatelqlikview]

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

      ;

[pradosh_thakur]

You are not following the way i have described . Please follow and it shall work.

[rkpatelqlikview]

Thanks for your time and help Pradosh,

Its working now