Combining columns

[#1]

hi

i am trying to combine 3 columns which i have broken down (or extracted ) from another column but it is not working for me

this image shows how i have broken it down and below is the code i used

[Closed Date] ,

     Right([Closed Date],4)  as daylyYear,

     Mid([Closed Date],5,3) as daylyMonth,

     Mid([Closed Date],9,2) as daylyDate,

daylyDate & ' ' & daylyMonth     <---  i tried combining like this but did not work.. i want to show the date as 01/05/2014 after combining .

would greatly appreciate some help

thank you in advance

[dinuwanbr]

Hi,

if you want to do other calculations in front end then use that expression in the calculation. Otherwise if you want it in script level load it as a separate field as follows using makedate function.

makedate(dailyYear,dailyMonth,dailyDate) as dailyClosed_date

-dinu'1

[#3]

tharidu i did try that in the script bt i am getting an error

this z my code below is t wrong??

dayly:

LOAD [Incident ID*+] as daylyIncident_ID,

     [Submit Date] as daylySubmit_Date,

     [Last Name+] as daylyLast_Name,

     [First Name+] as daylyFirst_Name,

     [Summary*] as daylySummary,

     [Status*]as daylyStatus,

     [Assigned Group*+] as daylyAssigned_Group,

     [Assignee+] as daylyAssignee,

     [Customer Site]as daylyCustomer_Site,

     [Closed Date] as daylyClosed_Date,

     [Last Modified By] as daylyLast_Modified_By,

     Right([Closed Date],4)  as daylyYear,

     Mid([Closed Date],5,3) as daylyMonth,

     Mid([Closed Date],9,2) as daylyDate,

   makedate(daylyDate,daylyMonth,daylyYear) as dClose_date,

  [First Name+] & ' ' & [Last Name+] As daylyName

FROM

[..\Downloads\2014 incidents.xls]

(biff, embedded labels, table is [Sheet 1$]);

[its_anandrjs]

Makedate function uses year,month,day in the function see the

eg:-

Makedate(Year,Month,Day)

For your field it is

MakeDate(Right([Closed Date],4) ,  Mid([Closed Date],5,3) , Mid([Closed Date],9,2)) as dClose_date,

because the fields  daylyDate, daylyMonth, daylyYear  are not created in the current table it will will be used in another table in any resident table.

[Anonymous]

try like

makedate(Mid([Closed Date],9,2),Mid([Closed Date],5,3),Right([Closed Date],4)) as dClose_date,

[#6]

Nisha i tried it but it doesn't show any values just an empty field

[#7]

i tried it but it doesn't show any values just an empty field

[MayilVahanan · Accepted solution]

Hi

Try like this.

You can achieve like this method also:

Map1:

Mapping Load * Inline

[

Month, MonthNum

Jan , 1

Feb, 2

Mar, 3

Apr, 4

];

Load  *, date#(daylyDate&'/'& Date(Date#(ApplyMap('Map1',daylyMonth),'MM'),'MM') &'/'&daylyYear,'DD/MM/YYYY') as dClose_date;

LOAD [Incident ID*+] as daylyIncident_ID,

     [Submit Date] as daylySubmit_Date,

     [Last Name+] as daylyLast_Name,

     [First Name+] as daylyFirst_Name,

     [Summary*] as daylySummary,

     [Status*]as daylyStatus,

     [Assigned Group*+] as daylyAssigned_Group,

     [Assignee+] as daylyAssignee,

     [Customer Site]as daylyCustomer_Site,

     [Closed Date] as daylyClosed_Date,

     [Last Modified By] as daylyLast_Modified_By,

     Right([Closed Date],4)  as daylyYear,

     Mid([Closed Date],5,3) as daylyMonth,

     Mid([Closed Date],9,2) as daylyDate,  

  [First Name+] & ' ' & [Last Name+] As daylyName

FROM

[..\Downloads\2014 incidents.xls]

(biff, embedded labels, table is [Sheet 1$]);

[#9]

thank you sooo mch it worked just fine...