I have been struggling with this issue for a while looking for a solution.
I am trying to load in data from 55 sheets from a total of 289 sheets in an excel spreadsheet.
I need the all columns from the 55 sheets to be transposed into one table using the CrosstTable load function except one column from each sheet to be the qualifier field. However, the qualifier field is not always the same for each sheet. I also want to use a loop as I do not want 55 different load statements in the script.
A few things to consider:
- The sheets do follow a specific naming convention but they will not change
- I know the column that is required for each sheet
I have provided an extract of what I am trying below - I think the IF statement is not the right way to go about it. Any help would really be appreciated.
for each vSheet in 'S1', 'D1' etc
CrossTable(x, value, 2)
'$(vSheet)' as table,
if('$(vSheet)' = 'S1', Column1,
if('$(vSheet)' = 'D1', Column2, '-')) as datafield,
(ooxml, embedded labels, table is $(vSheet), filters(
Yes you approach should work out. Your doubts around the IF is right - there is a better way to handle it.
I would suggest using a table - either inline or from a spreadsheet or CSV-file. The following code has not been tested - just write out from the top of my head. Anyways it gives you an indication of an approach that should work.
LOAD * INLINE [
nRows = NoOfRows('Sheets');
FOR i=1 TO nRows
vSheet = Peek('Sheet',i,'Sheets');
vCol = Peek('Col',i,'Sheets');
// pad the colums in the columnlist with @ before and after the column name, needed for correct Match later.