Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

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