Multiple Excel with Cartesian Product

Wed, 28 Oct 2015 15:09:34 GMT

I have a lot of xls files (1000+) with information in a table format. I need to concatenate this information. The date is in another page in the xls, in ONE cell for the whole table, so to "join" it to the table, i had used Join to make a cartesian product.

The problem I have is that the script finishes with table Buses and Table Buses-1. And in the other hand, it only makes the cartesian product with the first date loaded, the others are totally discarded.

I've tryied loading only the dates, at they are all loaded, but when I do the Cartesian Product (Join) all are discarded except the first one.

¿can anybody help me?

This is the structure of the xls files is:

a) Table "Ocupacion"

Bus	Servicio1	Number People	Total Capacity
1000	City1-City2	25	40
1232	City2-City3	34	41
...	...	...	...

b) Table Planilla

And in another sheet, there is a Cell with date: (Column 7, Row 2)

"25-20-2015"

This is the code I've used:

FOR each File in filelist ('Subidas y bajadas Turno\Subida *.xls')

[Buses]:

LOAD @1 as Bus,

@2 as Servicio,

@3 as [# Personal Transportado],

@4 as Capacidad,

filename() as Archivo

FROM [$(File)] (biff, no labels, header is 1 lines, table is [Ocupación$]);

Left Join (Buses)

LOAD

@7 as Fecha

FROM

[$(File)]

(biff, no labels, table is Planilla$) Where RecNo()=1;

NEXT File

Re: Multiple Excel with Cartesian Product

swuehl — Wed, 28 Oct 2015 18:24:13 GMT

Try like this:

FOR each File in filelist ('Subidas y bajadas Turno\Subida *.xls')

[Buses]:

LOAD @1 as Bus,

@2 as Servicio,

@3 as [# Personal Transportado],

@4 as Capacidad,

filename() as Archivo,

'$(File)' as File

FROM [$(File)] (biff, no labels, header is 1 lines, table is [Ocupación$]);

TMP:

LOAD

@7 as Fecha,

'$(File)' as File

FROM

[$(File)]

(biff, no labels, table is Planilla$) Where RecNo()=1;

LEFT JOIN (Buses)

LOAD * RESIDENT TMP;

DROP TABLE TMP;

topic Multiple Excel with Cartesian Product in QlikView

Multiple Excel with Cartesian Product

Re: Multiple Excel with Cartesian Product