Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Contributor
Contributor

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 

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Contributor
Contributor

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!