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: 
deerhunter
Creator
Creator

Dealing with CRLF

Were trying to read in a file that looks something like this:

FIELD1               FIELD2                    FIELD3

AAA               A1 A2 A3 A4          13% 11% 9% 6%

BBB               B1 B2 B3 B4          2% 22% 44% 5%

CCC               C1 C2 C3 C4         7% 33% 44% 32%

for each row, FIELD2 & FIELD3 are a single field with multiple entries separated by CRLF's, each are consumed as a single row as shown above, there are 3 rows in this example.

Looking to visualize with a table and be able to export to excel the data so that it looks similar to below:

FIELD1,FIELD2,FIELD3

AAA A1 13%

AAA A2 11%

AAA A3 9%

and so on to the EOF....

Any ideas?

Thanks in advance

Message was edited by: buck master

1 Solution

Accepted Solutions
deerhunter
Creator
Creator
Author

did not work, can you update the orig included qvf?

maybe i missed something

View solution in original post

8 Replies
marcus_sommer

If you could directly read the data from an excel or a database into the above mentioned fields you could split them per subfield() which worked without a third-parameter within a load like a loop or you could use a while-loop with iterno() as third-parameter for subfield. Therefore try something like:

load

     FIELD1,

     subfield(FIELD2, chr(13) & chr(10), iterno()) as FIELD2

     subfield(FIELD3, chr(13) & chr(10), iterno()) as FIELD3

from YourSource while iterno() <= substringcount(FIELD2, chr(13) & chr(10)) + 1;

- Marcus

deerhunter
Creator
Creator
Author

did not work, can you update the orig included qvf?

maybe i missed something

marcus_sommer

What didn't work? Any error-messages? Results are different as expected - if yes how does they look like? What is the source of these data?

Currently I haven't Sense available and couldn't therefore not take a look into it.

- Marcus

deerhunter
Creator
Creator
Author

I included a test app in the original post

marcus_sommer

Your app contained no data. Did you really try my suggestion?

deerhunter
Creator
Creator
Author

Very sorry i just added the data to the post.

Thanks

marcus_sommer

The delimiter within the fields isn't CRLF else only LF but the logic itself worked - see it within the attachment.

- Marcus

deerhunter
Creator
Creator
Author

Thanks that's perfect you have been a big help !