topic Re: Urgent help in QlikView

Urgent help

Thu, 21 Aug 2014 11:13:05 GMT

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

Re: Urgent help

Gysbert_Wassenaar — Thu, 21 Aug 2014 11:18:45 GMT

LOAD

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

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

FROM ....

Re: Urgent help

tresB — Thu, 21 Aug 2014 11:21:47 GMT

Load

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

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

From <>;

Re: Urgent help

Thu, 21 Aug 2014 11:27:05 GMT

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

Re: Urgent help

Gysbert_Wassenaar — Thu, 21 Aug 2014 11:27:26 GMT

Your date is off by 2000 years

Re: Urgent help

tresB — Thu, 21 Aug 2014 11:33:20 GMT

Thanks for the correction.

Corrected one:

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

Re: Urgent help

er_mohit — Thu, 21 Aug 2014 11:33:20 GMT

See the attached file

Re: Urgent help

ashfaq_haseeb — Thu, 21 Aug 2014 12:03:04 GMT

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

Re: Urgent help

Tue, 02 Sep 2014 06:24:50 GMT

Dear All thanks , its working.

Re: Urgent help

ashfaq_haseeb — Tue, 02 Sep 2014 06:27:28 GMT

Hi,

Can you close this thread by selecting appropriate answer.

Regards

ASHFAQ