This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
It seems I have troubles in solving following Task (in QV 11.20).
I have multiple invoices to load, which are xls-files, always from 2nd sheet (@2).
This would work fine, if I only had to load line 8-10 or 8-11 from sheet @2.
But: I need certain fields as column headers, in order to know the customer Name, Contract ID and invoicing period for each xls-file loaded.
This Information is (always) stated in Column A line 1,3 and 5.
Hence, I would need to create an additional column (I) = ContractID (stated in A1), column (J) = Period (stated in A3), and column (K) = CustomerName (stated in A5)
Do you have an idea of how to proceed? Thanks in advance!
Thx Peter! Very helpful.
My collegue finalized the task. Here's the code:
FOR EACH sFile IN FileList('C:\Temp\*.xls') Let sFile1 = '$(sFile)'; Header: LOAD * FROM $(sFile1) ( biff, no labels, Table is @2) WHERE RECNO() < 7 ; Let vZeile1 = PEEK('@1', 0, 'Header'); Let vZeile2 = PEEK('@1', 1, 'Header'); Let vZeile3 = PEEK('@1', 2, 'Header'); Let vZeile4 = PEEK('@1', 3, 'Header'); Let vZeile5 = PEEK('@1', 4, 'Header'); Invoice: LOAD *, '$(vZeile1)' AS ContractID, '$(vZeile3)' AS InvoicePeriod, '$(vZeile5)' AS Customer From $(sFile1) ( biff, no labels, Table is @2) WHERE not WildMatch(@1, '*ProductName*') and Len(@2)>'2' ; DROP Table Header; NEXT sFile
View solution in original post
Suggest to load in two steps,
First to load the header-info, write them into variables
Second to load the invoice and populate add fields with the above variables
This embedded in an loop
FOR EACH sFile IN ('....')
LOAD * FROM $(sFile) [string for xls or xlsx - no header] WHERE RECNO() < 5;
LET vContract = SUBFIELD(PEEK('@A', 1, 'Header'), 'Contract ID ', 2);
LET vInvoicePeriod = ....;
'$(vContract)' AS Contract,
'$(vInvoicePeriod)' AS InvoicePeriod,
FROM $(sFile) [string for xls or xlsx, embedded labels, header is 5 lines]
ProductName <> 'Total';