Loading specific field data from an Excel spreadsheet and creating QVD File

[#1]

I'm looking for some assistance please.

I need to load data from a multi-sheet excel workbook and then create a QVD file with the data. Please refer to my sample workbook.

Each sheet contains header information in the same cells. i.e Cell C3 = Company Name; Cell H3 = Date; H4 = Quote Reference; H6 = Rep Code.

Then I have multiple transaction lines. The "Our Reference", "Quantity" and "Unit Price" is the column data I need.

I need to take this data and load it into a QVD file as line transactions i.e

Transaction Line 1 should be loaded as "Company Name" + "Date" + "Quote Reference" + "Rep Code" + "Our Reference" + "Quantity" + "Unit Price"

Transaction Line 2 should be the same except for the "Our Reference"; "Quantity" and "Unit Price" pertaining to that specific transaction.

Once I have loaded all of the data for the sheet, I need to move onto the next sheet and append that data to the same QVD file.

Thank you

Craig

[Anonymous · Accepted solution]

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.

Jonathan

[#3]

Thanks Jonathan.

Much appreciated.

Regards

Craig

[#4]

Hi Jonathan,

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.

Regards

Craig

[Anonymous]

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'.

Jonathan