topic Multiple Data in Cell to columns in App Development

Multiple Data in Cell to columns

petergassert — Sat, 16 Nov 2024 16:51:54 GMT

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

Re: Multiple Data in Cell to columns

chrismarlow — Thu, 06 Jan 2022 18:40:20 GMT

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.

Re: Multiple Data in Cell to columns

petergassert — Fri, 07 Jan 2022 09:58:16 GMT

Thanks Chris!

Forgot this possibility of the subfield function.

Thanks and a all the best for 2022,

Peter