Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
f1234567
Contributor III
Contributor III

Load multiple XLS and use certain fields as headlines for tables

Hi QV-experts,

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!

1 Solution

Accepted Solutions
f1234567
Contributor III
Contributor III
Author

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

2 Replies
prieper
Master II
Master II

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

(aircode)

FOR EACH sFile IN ('....')

Header:

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 = ....;

Invoice:

LOAD

*,

'$(vContract)'     AS Contract,

'$(vInvoicePeriod)' AS InvoicePeriod,

...

FROM $(sFile) [string for xls or xlsx, embedded labels, header is 5 lines]

WHERE

ProductName <> 'Total';

NEXT sFile

HTH

Peter

f1234567
Contributor III
Contributor III
Author

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