How can I limit the information loaded by Crosstable?

[Anonymous]

Hello,

I´m loading several folder including one Excel files per month of sales. Each file has the following format

I manageed to load the sales with the following code:

CrossTable(Sucursal, Vta_Diaria)

LOAD *

FROM [lib://AÑO 2017/VENTAS*.xlsx]

(ooxml, embedded labels, table is Hoja1) Where(not IsText(FECHA))

The problem I am facing is that the column H is being loaded in a file named 'H' including the SUM() of all the sales of that month.

Is there any way of limiting the crosstable load to avoid loading those columns?

Thank you in advance for any sguidance on this issue.

[marcus_sommer]

Just don't load the fields with a wildcard * else specify each column explicitely.

- Marcus

[Anonymous]

Thank you Marcus.

I loaded the field with a wildcard since the number of locations (columns) is variable in each file. If I define each location, some fields are not located in all the files and can´t load correctly.

[marcus_sommer]

In such cases you could use a multi-step load-approach with loading at first just the fieldnames of the files, for example with:

first 1 load * from Source;

and then checking which fields exists and then switching between several different load-statements or creating ones on the fly. Of course such an approach isn't quite trivial and needs some efforts to develop them.

Easy would it be to separate the different file-structures in different folders so that you could work with a few different but quite simple load-statements - I use this approach quite often.

Another way which might be useful in your case is just to filter these values. If you exclude the record with 'SUB-TOTAL' there would be no value in column H. I'm not sure if the column would then be directly skipped but within an afterward load these empty cells could be identified and removed.

- Marcus