Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
did not work, can you update the orig included qvf?
maybe i missed something
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
did not work, can you update the orig included qvf?
maybe i missed something
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
I included a test app in the original post
Your app contained no data. Did you really try my suggestion?
Very sorry i just added the data to the post.
Thanks
The delimiter within the fields isn't CRLF else only LF but the logic itself worked - see it within the attachment.
- Marcus
Thanks that's perfect you have been a big help !