Are you skipping titles and lines Rows by category, and using Header rows so you get the field names?
If you cannot, you can use
...SubField(Field, '!', 1) AS Blank,SubField(Field, '!', 2) AS Field1,SubField(Field, '!', 3) AS Field2...
to get the fiels loaded separately over the code that the Connector generates.
Hope that helps.
Thank you for the reply. Your solution is working, but it is tedious.
So, instead we are skipping all the unwanted rows and only considering the Header row and Data rows by categorization. By doing so system is able to delemit first 4 columns as per the default field delimeter '!', but 5th column onwards default field delimeter fails to delemit.
The correction on this is a bit tedious.
Initially my workaround was to split the fields manually in my script using the delimiters. Something like:
This method worked perfectly for me on some reports but when testing the results, on larger reports, I found some rows with data shifted into the wrong column. These rows contained the delimiter [!] in the data and in the very column that started the concatenation.
The preferred method that worked across the board for my solution:
After you “Get” the report in the report builder and you have completed defining which rows are to be ‘Skipped’ and which are ‘Headers’, you need to count the number of fields that exist between the last delimiter and the point where the next should be. Insert that number (in the example below from 153 to 194) into the ‘Field delimiter after position box’ -> Click Add. A new separator appears in the sample view and the script is updated. Repeat until all your columns have been included...
The script that is created before looks something like:
Select SAPREPORT (REPORT (MYREPORT), VARIANT (QV_VARIANT),ROWS_PER_RECORD (1), FIELD_DELIMITER_POSITIONS (0, 11, 16, 27, 40, 48, 56, 65,96, 113, 142, 153), SKIP (T (1)),HEADER (P (0, "!Header !")));
Note FIELD_START_POSITIONS in the new script:
Select SAPREPORT (REPORT (MYREPORT), VARIANT (QV_VARIANT), ROWS_PER_RECORD (1),FIELD_DELIMITER_POSITIONS (0, 11, 16, 27, 40, 48, 56, 65, 96, 113, 142, 153), FIELD_START_POSITIONS (194, 195, 204, 205),SKIP (T (1)), HEADER (P (0, "!Header !")));
Finally, You need to remove the delimiter from these in your script:
PurgeChar([Field12_ThisField],’!’) as [Field12_ThisField]
Hope it helps.
I see that his is an old post, but for anyone who still needs a suggestion --- I had the same problem until I realized that when you are omitting the rows that you don't want, you have to hit the Apply button for each type of row. That is, if you don't want rows with the type 1, select that row, click the box that says to omit all rows of that type, then press Apply. Then go to the next type, select it, and press Apply. Don't wait until you have selected all of the rows to omit.
You can tell if this is working when you see the lines disappear in the lower window as you hit the Apply button.
Once it figured this out, all of the columns came out the way they should, and QlikView recognized the delimiter.