Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Creator

## SubField (product) of 2 columns

Hello!

I have a table:

ID, Col2, Col3:

1, 11|22|33, -Text1 -Text2 -Text3

2, 15|25|35, -Text4 -Text5 -Text6

3, , -Teext7 -Text8 -Text9 -Text10

Row separator for Col2 is '|'

Row separator for Col3 is '-'

I need to make separate rows from Col2 and Col3, so that each value of Col2 goes together with value (corresponds) of Col3 in each row.

So the result should be:

ID, Col2_, Col3_:

1, 11, Text1

1, 22, Text2

1, 33, Text3

2, 15, Text4

2, 25, Text5

2, 35, Text6

3, , Teext7

3, , Teext8

3, , Teext9

3, , Teext10

How can I do that?

Thanks!

1 Solution

Accepted Solutions
MVP

Change only the separator character like:

SubField(Col3,'|',IterNo()+1) as NewCol3

9 Replies
MVP

May be this:

Table:

Col2,

Col3

Where Len(Trim(Col3)) > 0;

SubField(Col2, '|') as Col2,

If(Len(Trim(Col2)) = 0, SubField(Col3, '-'), SubField(Col3, '-', AutoNumber(RowNo(), ID) + 1)) as Col3;

ID, Col2, Col3

1, 11|22|33, -Text1 -Text2 -Text3

2, 15|25|35, -Text4 -Text5 -Text6

3, , -Teext7 -Text8 -Text9 -Text10

];

MVP

Try:

Table:

SubField(Col2,'|',IterNo()) as NewCol2,

SubField(Col3,'-',IterNo()+1) as NewCol3

While IterNo()<= RangeMax(SubStringCount(Col3,'-'),SubStringCount(Col2,'|'));

ID, Col2, Col3

1, 11|22|33,-Text1-Text2-Text3

2, 15|25|35,-Text4-Text5-Text6

3, ,-Teext7-Text8-Text9-Text10

];

Creator
Author

Both ways work quite well.

2 questions: where (and how) did you learn these tricks?

and the serious one:

Could you change it for another source data:

ID, Col2, Col3

1, 11|22|33, Text1| Text2| Text3

2, 15|25|35, Text4| Text5| Text6

3, , Teext7| Text8| Text9| Text10

];

where '|' is a break line char ( char(10) ) in Excel, So that we have in Excel   Col2 cell

11

22

33

in 1 cell ( Col2 )

and corresponding:

Text1

Text2

Text3

in 1 cell

in Col3 cell to the right of the Col2 cell,

in the same row with ID = 1

etc....

Creator
Author

OK I've figured out how to amend

MVP

With mine, may be this?

Table:

Col2,

Col3

Where Len(Trim(Col3)) > 0;

SubField(Col2, Chr(10)) as Col2,

If(Len(Trim(Col2)) = 0, SubField(Col3, '-'), SubField(Col3, '-', AutoNumber(RowNo(), ID) + 1)) as Col3;

Not tested it, but see if that works

MVP

Change only the separator character like:

SubField(Col3,'|',IterNo()+1) as NewCol3

Specialist III

Could you please try below sample script:

```For i=1 to 4
Data:
SubField(Col2,'|',\$(i)) AS Col2,
SubField(Col3,'-',\$(i)) AS Col3
FROM
Source.txt
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Next

NoConcatenate

FinalData:
Resident Data
WHERE Len(Col3) > 0;

Drop Table Data;
```
Creator
Author

So could you refer me to a study course or book or whatever to learn this and similar tricks?

MVP

Curious to know if my response worked?

and as per ways to learn these tricks, I am not sure how tresesco‌ learnt them, but I learned by helping others. The more you get involved on the community, the more diverse issues you will come across. You will have a solution, someone else have a better solution (like Tresesco did this time) and you will see why there solution was more efficient and re engineer it to understand it. In the process you get to learn more. It won't happen over-night, but as you spend more and more time you will get to learn new tricks.

Community Browser