Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pgalvezt
Specialist
Specialist

Read Excel and sort the data

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,

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

Hi,

Like this?

Capture.JPG

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

View solution in original post

5 Replies
settu_periasamy
Master III
Master III

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

Capture1.JPG

Note:

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

pgalvezt
Specialist
Specialist
Author

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!

pgalvezt
Specialist
Specialist
Author

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!

settu_periasamy
Master III
Master III

Hi,

Like this?

Capture.JPG

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

settu_periasamy
Master III
Master III

Hi,

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