How to concatenate an unspecified number of rows?

[Dsmithvax]

Hello everyone I was hoping you could help me with a problem I have developing an App. 

We are receiving lots of PDF invoices that are then converted to excel file. I then need to pick them up en mass and present them in qlikview for processing. I have taken multiple excel files into Qlikview before - I don't have a problem with that part of the process but unfortunately due to the nature of the PDF's, occasionally it will spit out an excel with a different number of rows. This is because there are a large number of blank columns in between the data I need and sometimes more or less are produced. It is usually anywhere between 40 or 50 columns, with 8 having data that I need and the rest blank.

Fortunately the order of the columns with data in never changes, I just need to filter out a variable number of blank ones, I wanted to concatenate all fields into one and then use delimiters and trim to extract the data I need. But I do not know how to tell qlikview to concatenate all fields in a load without specifying them. 

Is there anyway to do this? I need something like concat(*) in the same way load * works. 

Thanks in advance

[marcus_sommer]

You could load the excel without the labels and using their column-index for the concat-task, maybe like:

load @1 & '|' & @2 … from Excel; // by biff-fileformat respectively xls-files

load A & '|' & B … from Excel; // by ooxml-fileformat respectively xlsx-files

- Marcus

[Dsmithvax]

Hi Marcus, 

Thank you for the reply, but unfortunately this does not work. I have to specify a number of columns to concatenate here, and if I put to many it will not work because it can't find that field name. So in examples where the file comes out with a smaller number of columns, this would break the load. 

Cheers 

[marcus_sommer · Accepted solution]

In this case you will need a pre-load like:

T: first 1 load * from Excel;

and then you could get the number of fields with something like:

let #Fields = nooffields('T');

and you may also create a loop with it like:

for i = 1 to nooffields('T')
   let vFields = '$(vFields)' & fieldname($(i), 'T') & '|';
next

You might need here and there a few adjustments to the syntax and/or the logic to make it suitable to your task and you may also extent it with further features like fieldvaluecount() to check if there is any content within a field (for this you need of course to load more as the first record and it may in general an alternative approach (loading everything with a wildcard and then check the fieldvaluecount to drop the empty field within a loop like mentioned above)).

- Marcus

[Dsmithvax]

Hi Marcus, 

This is exactly the kind of thing I was looking for, thank you so much! I will go and implement it now.

Cheers!