Looping through a table to convert a list field into a new table

[#1]

Hi all,

I am loading a table into QV that contains a 'Program' field which has multiple values in it of the form:

"P00249; P00282; P00084"

I would like to be able to take my table, loaded as 'RAGInfo', and loop through each project, going through that field, breaking it down and storing into a new table.

So for the above example,

I would have 3 rows for the one project:

Project Program
D01234 P00249
D01234 P00282
D01234 P00084

Can anybody show me how I can code this action.
I'm guessing some kind of FOR loop

Thanks in advance,

Matt

[#2 · Accepted solution]

Matt,

You can use a 'for each / next' loop - see 'help'

Another method depending on the data structure is simply to use subfield to break the data up during a load - cut and paste the following into a test document to see what I mean:

test1:
LOAD * INLINE [
F1
s1;s2;s3;s4;s5
];

load subfield(F1, ';') as F2
resident test1

Hope this helps.

Gordon

[#3]

Thanks Gordon, thats great.

I have never come across the subfield function before, but it worked perfectly.

Many thanks for your suggestion.

Matt

[#4]

Hi Gordon,

I have the exact same scenario, but in reverse. He wanted to extract 3 rows of data from the 1 row. I want to combine 3 rows of data into 1 row.

Any ideas?

Thanks,

-Kevin

[#5]

Simply concatenate the rows as :