parse string into different columns

geof

Hi,

I need your help to parse a string of data into table.

Attached are example of Raw_data table and the RSLT table I want to achieve.

For each EQP I may have a string with couple of (Position:Value) separated by ";" like

P:V;P:V;P:V;

Thanks for your help

Geof

Raw_data.jmp

RSLT.jmp

pmroz

This will do it.  I'm processing lists within lists using the WORDS function.

dt = New Table( "Raw_data",

    Add Rows( 6 ),     New Column( "EQP", Character( 1 ), Nominal,

        Set Values( {"A", "B", "A", "C", "A", "B"} )

    ),

    New Column( "Position : Value", Character, Nominal,

        Set Values(

            {"362:53;394:134;", "227:37;247:152;122:44;", "96:88;", "266:48;",

            "362:64;394:136;", "266:47;"}

        )

    )

);

// Create an empty results table

rslt_tbl = New Table( "Results", New Column( "EQP", Character( 1 ), Nominal ),

    New Column( "Position", Numeric, Continuous, Format( "Best", 12 ) ),

    New Column( "Value", Numeric, Continuous, Format( "Best", 12 ) )

);

m = 0;

for (i = 1, i <= nrows(dt), i++,

    one_eqp = dt:eqp[i];

   

    pos_val_list = words(column(dt, "Position : Value")[i], ";");

   

    for (k = 1, k <= nitems(pos_val_list), k++,

        one_pos_val_list = words(pos_val_list[k], ":");

        one_pos = num(one_pos_val_list[1]);

        one_val = num(one_pos_val_list[2]);

        m++;

        rslt_tbl << add rows(1);

        rslt_tbl:eqp[m]      = one_eqp;

        rslt_tbl:Position[m] = one_pos;

        rslt_tbl:Value[m]    = one_val;

    );

);