Good morning,
Is it possible in JMP to join several columns in 1 single line from an identifier, in other words: go from the first table to the second?
THANKS
| NAME | First name | item 1 | item 2 | item 3 | item 4 | | | | | | | | |
| frzegfze | he did | A | 12 | 224 | a | | | | | | | | |
| he did | he did | B | 3 | 5 | b | | | | | | | | |
| he did | he did | C | 4 | 67 | c | | | | | | | | |
| GRZGR | xcsq | D | 5 | 5 | d | | | | | | | | |
| GZE | GFEZ | E | 2 | 31 | e | | | | | | | | |
| GZE | GFEZ | F | 7 | 2 | f | | | | | | | | |
| GZE | QD | G | 8 | 0 | g | | | | | | | | |
| | | | | | | | | | | | | | |
| NAME | First name | item 1-1 | item 2-1 | item 3-1 | item 4-1 | item 1-2 | item 2-2 | item 3-2 | item 4-2 | item 1-3 | item 2-3 | item 3-3 | item 4-3 |
| frzegfze | he did | A | 12 | 224 | a | | | | | | | | |
| he did | he did | B | 3 | 5 | b | C | 4 | 67 | c | | | | |
| GRZGR | xcsq | D | 5 | 5 | d | | | | | | | | |
| GZE | GFEZ | E | 2 | 31 | e | F | 7 | 2 | f | G | 8 | 0 | g |
| GZE | QD | G | 8 | 0 | g | | | | | | | | |
in the second table, "G" shows up twice. with GFEZ and QD?
You could stack and then split the data.
To stack the columns, they have to have the same format. So maybe some additional step are necessary to align the formats and set them back to the original format.
dt = New Table( "demo",
Add Rows( 7 ),
Compress File When Saved( 1 ),
New Column( "NAME",
Character,
Set Values( {"frzegfze", "he did", "he did", "GRZGR", "GZE", "GZE", "GZE"} )
),
New Column( "First name",
Character,
Set Values( {"he did", "he did", "he did", "xcsq", "GFEZ", "GFEZ", "QD"} )
),
New Column( "item 1",
Character,
Set Values( {"A", "B", "C", "D", "E", "F", "G"} )
),
New Column( "item 2",
Character,
Set Values( {"12", "3", "4", "5", "2", "7", "8"} )
),
New Column( "item 3",
Character,
Set Values( {"224", "5", "67", "5", "31", "2", "0"} )
),
New Column( "item 4",
Character,
Set Values( {"a", "b", "c", "d", "e", "f", "g"} )
)
);
dt << New Column( "entry",
Formula( Col Rank( Row(), :NAME, :First name ) ),
);
dtstacked = dt << Stack(
columns( :item 1, :item 2, :item 3, :item 4 )
);
dtsplit = dtstacked <<
Split(
Split By( :entry, :Label ),
Split( :Data ),
Group( :NAME, :First name )
);
