Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Dsmithvax
Contributor
Contributor

How to concatenate an unspecified number of rows?

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

 

 

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
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
Contributor
Contributor
Author

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

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
Contributor
Contributor
Author

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!