Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I got a table with multiple values in one and more cells.
All values are Separated by ' ' and i do not know how many values are given in one cell.
Desc | C1 | C2 | C3 |
A | 1,2 3,4 | 5,6 7,8 | 9,10 |
B | 1,2 3,4 5,6 | 5,6 7,8 | 9,10 11,12 |
C | 1,2 3,4 5,6 | 5,6 7,8 9,10 | 11,12 13,14 |
D | A,B | B,C C,D | |
E | A,B B,C | C,D D,E | E,F |
Main Question is, after concatenation of C1, C2 and C3 to transfer a respective cell?
Example for value A:
Desc | Value |
A | 1,2 |
A | 3,4 |
A | 5,6 |
A | 7,8 |
A | 9,10 |
Is a function given simular to Excel to transfer data to columns and then may pivot the data in script?
Thanks,
Peter
Hi,
You might need to do something about the empty values, but how about the below for a start;
data:
load
Desc,
Trim(SubField(C1&' '&C2&' '&C3, ' ')) as Value;
load * inline [
Desc|C1|C2|C3
A|1,2 3,4|5,6 7,8|9,10
B|1,2 3,4 5,6|5,6 7,8|9,10 11,12
C|1,2 3,4 5,6|5,6 7,8 9,10|11,12 13,14
D|A,B|B,C C,D|
E|A,B B,C|C,D D,E|E,F
] (delimiter is '|');
Cheers,
Chris.
Hi,
You might need to do something about the empty values, but how about the below for a start;
data:
load
Desc,
Trim(SubField(C1&' '&C2&' '&C3, ' ')) as Value;
load * inline [
Desc|C1|C2|C3
A|1,2 3,4|5,6 7,8|9,10
B|1,2 3,4 5,6|5,6 7,8|9,10 11,12
C|1,2 3,4 5,6|5,6 7,8 9,10|11,12 13,14
D|A,B|B,C C,D|
E|A,B B,C|C,D D,E|E,F
] (delimiter is '|');
Cheers,
Chris.
Thanks Chris!
Forgot this possibility of the subfield function.
Thanks and a all the best for 2022,
Peter