Urgent help

[#1]

Dear All

I need suggestions over below field, where i have to split EMPIDDATE field on the basis of EMP ID & DATE

EMPIDDATE

1[01$022&014]

2[11$042&014]

3[14$022&014]

4[04$012&014]

5[05$092&014]

Needed O/P:

EMPID    DATE

1          01 -02-2014

2          11-04-2014

and So on..

Thanks in Advance

Timba

[Gysbert_Wassenaar]

LOAD

     subfield(EMPIDDATE,'[',1) as EMPID,

     date(date#(purgechar(textbetween(EMPIDDATE,'[',']'),'&') ,'DD$MMYYYY'),'DD-MM-YYYY') as DATE

FROM ....

[tresesco]

Load

          SubField(EMPIDDATE, '[',1) as EMPID,

          Date(date#(SubField('1[01$022&014]', '[',2), 'DD$MMY&YYY]')) as DATE

From <>;

[#4]

HI, use

following expression in script in in list box,

left(purgechar(EMPIDDATE,'[]$&'),1) as EMDID,

Makedate(right(purgechar(EMPIDDATE,'[]$&'),4),mid(purgechar(EMPIDDATE,'[]$&'),4,2),mid(purgechar(EMPIDDATE,'[]$&'),2,2)) as Date

[Gysbert_Wassenaar]

Your date is off by 2000 years

[tresesco]

Thanks for the correction.

Corrected one:

Date(date#(Replace(SubField('1[01$022&014]', '[',2),'&',''), 'DD$MMYYYY]')) as DATE

[er_mohit]

See the attached file

[ashfaq_haseeb]

Hi,

try below

Directory;

LOAD EMPIDDATE,

Left(EMPIDDATE, Index(EMPIDDATE, '[') - 1) as EMPID,

Date(date#(right(KeepChar(EMPIDDATE,'0123456789'),8),'DDMMYYYY')) as Date

FROM

[..\Desktop\Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards

ASHFAQ

[#9]

Dear All thanks , its working.

[ashfaq_haseeb]

Hi,

Can you close this thread by selecting appropriate answer.

Regards

ASHFAQ