Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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$);
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..
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!
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!
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$);
Hi,
Would you mind to close this thread, if you got the answer?