Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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!