Discussion Board for collaboration on QlikView Scripting.
I've got the problem, that I have CSV file with the structure:
The list of values continues a lot of times. So this is only an example.
On loading the CSV, I would like to have a table like this:
Any other blocks in the CSV file should result in an additional column. The header rows and the blank lines should be skip!
Does anybody has an idea to accomplish that.
Go to Solution.
please see attached solution. The whole ETL process is done with several steps:
1. load related lines
2. mark blocks for columns
3. split data lines into fields
4. transpose with generic load
5. create target table
6. collect auto created generic tables
7. join everything together to finalize target table
this is possible. You have to read the whole reacord as line and parse the columns of the data lines with subfield().
You have to switch between header and data section on a condition.
After you have read the whole file you can transpose the lines into positions and columns.
It makes only sense to develop an example on the real data. Maybe you can attache a file.
here is an example file from the original data.
wow, that's really great and exactly what I was looking for!
But there is an additional question. The code only reads data from 2012. Is there a more general way, which also works with 2013 data?
Thanks a lot for your effort!
Yes, of course. Just replace the wildmatch in Step1 with:
LOAD if(wildmatch(Line, '* 201?,*', -1, 0), subfield(Line, ',', 1), peek(Col)) as Col, Line
now it's exactly what I needed!