Sure, no prob.
To be able to loop on the different sheets, then we have to know all of their labels.
This can be done by reading the excel document with ODBC and then calling SQLTABLES.
The connection requires to specify the path of the excel document which I'm putting in variable vTargetPath (used also in the load statement).
You can add an "exit script" after the disconnect, reload and check the tableviewer (ctrl+t) to actually see the content of the created table.
Now that we have all the labels in a table, and since our sheets all have same structure, we only have to write a load statement once within a for..next and provide the sheet label in a variable instead of it being hardcoded, and the data will be concatenated automatically.
So in each iteration i, vSheet will contain the sheet label in the correct format (without the $, that's what the PurgeChar function does, it removes the characters that we specify, here it's the $).
To read the sheet label from the table, I use the peek function for which you provide the field name (TABLE_NAME), the index (i, so 0 is the first row, 1 the second, and so on) and the table from which we're reading (sheets that we loaded previously).
Now in this case, we also have to create a date from the sheet label that is formatted as MMMYY.
So I use the Date# function to provide the input format then the Date function with the needed output format (omit the output format and it takes it from the environment variable DateFormat specified at the very start of the script).
Actually it'll directly be treated as a start of month, so we can omit the MonthStart function in this case.
The Floor function removes the time part in a date (like DayStart function) and stores the value as a number (for example 41832 for July 12, 2014).
I personally always use it for storing dates as their format can be adjusted later on the UI and this saves from handling formats within expressions.
But you can omit this too if you want the values to appear as dates directly, in which case you just need to:
- redefine vDate as: let vDate = Date(Date#('$(vSheet)', 'MMMYY'));
- surround the $(vDate) in the load with single quotes like this '$(vDate)'.
The Load part is normal, in which we put our fields created from vDate (year, month, date) and the ones from excel (*).
vSheet and vDate are adjusted dynamically on each iteration.
Hope this clarified the steps.
If you feel something's still not clear and you need further detail, let me know.
If you mean the $ that we are purging with PurgeChar, you can check the values of field TABLE_NAME, you'll see that you have Dec13$, Jan14$, Feb14$.
If we load the excel sheet with vSheet having those values, an error is raised.
In a normal load from an excel (without using a variable), you can notice that the sheet name is Dec13.
So what we do is to provide same naming to the variable by removing the $.
Otherwise if you mean the $ before the variable, it replaces it with its value (dollar sign expansion).
If it should be considered as string/characters, we surround it with single quotes.
Within the load statement, if we don't put $, vDate will be considered as a field name and an error is raised.
If you omitted the floor function in vDate (so you have it as Date(...)), its value would be 1/12/2013, in which case we should add the single quotes.
This can be a bit tricky, so I suggest you also check the below:
- search for "dollar sign expansion" in qlikview help