topic Read Excel and sort the data in QlikView

Read Excel and sort the data

pgalvezt — Thu, 31 Mar 2016 18:11:38 GMT

Hello,

Pls, I need to read an excel file, What I want is extract "lunes, 15 de febrero de 2016" Transform in "15-02-2016". Then "15-02-2016" move to A8 in qlikview.

Same thing with the other date but (The final date) left in B8. And 1-6 files have to dissapear.

Thanks,

Re: Read Excel and sort the data

settu_periasamy — Fri, 01 Apr 2016 03:22:11 GMT

Hi,

Try this..

Directory;
Temp:
First 2
LOAD D
FROM
Test.xlsx
(ooxml, no labels, header is 4 lines, table is [What I Have]);

Let [Fecha Inicio]=Peek('D',0);
Let [Fecha Termino]=Peek('D',1);

DROP Table Temp;

Final:
LOAD '$(Fecha Inicio)' as [Fecha Inicio],
'$(Fecha Termino)' as [Fecha Termino],
TIT,
Nombre,
Port,
Dia1,
Dia2,
Dia3,
Dia4,
Dia5,
Dia6,
Dia7
FROM
Test.xlsx
(ooxml, embedded labels, header is 6 lines, table is [What I Have]);

Note:

You can convert Fecha Inicio, Fecha Termino field to Date format based on your settings..

Re: Read Excel and sort the data

pgalvezt — Fri, 01 Apr 2016 12:44:42 GMT

Hi settu_periasamy,

Thank you for your reply, Fecha Inicio show nothing.

I know that works, but doesn't work for me, I attached my xls and qvw.

Thanks!

Re: Read Excel and sort the data

pgalvezt — Fri, 01 Apr 2016 13:17:02 GMT

Hi, I got it, The thing is:

I change the first function of 2 to 10, and I changed the peek function values so now the field show me the data. Very nice.

Just one more question... I would to show 15_02_2016 instead Fifteen 15th, 2016... (Date Format)

Pls your help,

Thanks!

Re: Read Excel and sort the data

settu_periasamy — Fri, 01 Apr 2016 17:31:26 GMT

Hi,

Like this?

DIRECTORY;

Temp:
First 2
LOAD @2
FROM
Test2.xls
(biff, no labels, header is 1 lines, table is GENERAL$);

Let [Fecha Inicio]=Peek('@2',0);
Let [Fecha Termino]=Peek('@2',1);

DROP Table Temp;

Final:
LOAD
Date(Date#(replace(SubField('$(Fecha Inicio)',', ',-1),' de ',' '),'DD MMMM YYYY'),'DD_MM_YYYY') as [Fecha Inicio],
Date(Date#(replace(SubField('$(Fecha Termino)',', ',-1),' de ',' '),'DD MMMM YYYY'),'DD_MM_YYYY') as [Fecha Termino],
Rut,
Nombre,
H.Pactadas,
Lunes,
Martes,
Miercoles,
Jueves,
Viernes,
Sabado,
Domingo,
Semanales
FROM
Test2.xls
(biff, embedded labels, header is 3 lines, table is GENERAL$);

Re: Read Excel and sort the data

settu_periasamy — Fri, 01 Apr 2016 17:47:07 GMT

Hi,

Would you mind to close this thread, if you got the answer?