Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple Excel with Cartesian Product

Hi

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"

BusServicio1Number PeopleTotal Capacity
1000City1-City22540
1232City2-City33441
............

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

1 Reply
swuehl
MVP
MVP

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;

NEXT

LEFT JOIN (Buses)

LOAD * RESIDENT TMP;

DROP TABLE TMP;