Please find attached. To explain:
You loop through each sheet and ....
1. Load the headers into a mapping table
To be able to select the fields in step 3.
2. Load in the rows from row 9 onwards
Ignored rows where descript column was blank or 'Description'. This should have been done using a conditional delete on the Load statements transformation step but the label names were a problem as deleting the top row.
3. Join on header information
Autogenerate 1 loads one row only. Second parameter identifies the field in column F, and returns column H.
Because the different fields are listed on different rows instead of as different columns you could use an if statement to ask what row is being looked at (column F) to determine which value is being set (column H).
4. Concatenate the result onto a Quotes table and delete the TempTable being used up to now
Please note that the Total price column is messed up because of the rand currency. I'll let you grapple with that one though.
Hope this helps.
Excel mutliple sheets.qvw 164.2 K
You have helped me some, thank you. Just two problems. Each Sheet has different header details. When the load script does the autogenerate it loads the last header details for all of the items. How do I get around this?
Secondly, the example has 4 sheets. How would I load if there are an unknown number of sheets.
Thanks for your help.
The attached solves the first issue by adding the WorkSheet name into the mapping table and the applymap() functions. Seems these mapping tables aren't replaced on the next pass through the loop, and can't be dropped.
I'm not sure on the second problem at the moment. I'll have a think about it, but it might be worth you sending a fresh mail out to QlikCommunity just about that ... ask something like 'can you loop thorugh or compile a list of all the worksheet labels without knowing their names before hand'.
Excel mutliple sheets 2.qvw 164.5 K