Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to split one row to several based on a coilumn. Here is the example
The input is like this
ID | Data1 | Data2 | Data3 |
---|---|---|---|
1 | A | X85 | 15 |
2 | B | D12 | 10 |
3 | C | F40/E02 | 12/50 |
4 | D | W16/R70 | 42/55 |
And I need to have new table where rows with slash in Data2 column will be splited to several rows. So output should be like this
Header 1 | Header 2 | Header 3 | Header 4 |
---|---|---|---|
1 | A | X85 | 15 |
2 | B | D12 | 10 |
3_1 | C | F40 | 12 |
3_2 | C | E02 | 50 |
4_1 | D | W16 | 42 |
4_2 | D | R70 | 55 |
Currently I'm using the FOR statement to go through all the lines of original table and adding new line when slash is found. But this is very slow. Isn't there any function which can help me to do it much faster?
Thanks in advance,
Jindra
A bit of tricks in the script. PFA
Try something like this:
Input:
load
text(if(SubStringCount(Data3,'/'), ID & '_' & dummy,ID)) as ID
, Data1
, Data2
, SubField(Data3,'/',dummy) as Data3;
load * , AutoNumber(Data2,ID) as dummy;
load ID, Data1, subfield(Data2,'/') as Data2, Data3 INLINE [
ID, Data1, Data2, Data3
1, A, X85, 15
2, B, D12, 10
3, C, F40/E02, 12/50
4, D, W16/R70, 42/55
];
You can replace the inline load with something that loads data from a database or file.
A bit of tricks in the script. PFA
Thanks to both of you for such quick response. Both of answers work great, tresesco solution is also faster.