SubField (product) of 2 columns

[qluser01]

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!

[sunny_talwar]

May be this:

Table:

LOAD ID,

  Col2,

  Col3

Where Len(Trim(Col3)) > 0;

LOAD ID,

  SubField(Col2, '|') as Col2,

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

LOAD * Inline [

ID, Col2, Col3

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

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

3, , -Teext7 -Text8 -Text9 -Text10

];

[tresesco]

Try:

Table:

load *,

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

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

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

load * Inline [

ID, Col2, Col3

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

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

3, ,-Teext7-Text8-Text9-Text10

];

[qluser01]

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....

[qluser01]

OK I've figured out how to amend  Tresesco Bs version for my needs.

The only question now is:  HOW?

What to study to learn such tricks?

Thanks!

[sunny_talwar]

With mine, may be this?

Table:

LOAD ID,

  Col2,

  Col3

Where Len(Trim(Col3)) > 0;

LOAD ID,

  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

[tresesco · Accepted solution]

Change only the separator character like:

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

[rahulpawarb]

Could you please try below sample script:

For i=1 to 4
Data:
LOAD ID, 
     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:
LOAD *
Resident Data
WHERE Len(Col3) > 0;


Drop Table Data;

[qluser01]

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

[sunny_talwar]

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.