Creating multiple transactional lines from one line

[#1]

Hi everyone,

I am having trouble with a(n) (intelligent) looped statement which looks into a specific field and generates multiple trnasactional rows from it. Has anyone come across this and found a cool way of resolving it.

For example, I have a table such as:

TxnID	Product	Info
A1	Bananas	Yellow
A2	Apples	Green;Red
A3	Pears	Yellow; Brown; Green

and I want to get this out of it:

TxnID	Product	Info
A1	Bananas	Yellow
A2	Apples	Green
A2	Apples	Red
A3	Pears	Yellow
A3	Pears	Brown
A3	Pears	Green

Naturally, the amount of ";" separated values in the original Info column changes depending on the table loaded and therefore, needs to change accordingly. Use of the "textBetween" function cannot be "hard-coded".

Any suggestions?

Cheers.

[tresesco]

Use SUBFIELD() function in the script like

Load
      TxnID,
      Product,
      SubField(Info, ' ; ') as NewInfo
From ...........

Cheers.

Thanks.

[#3]

Hi Tresesco,

Subfield() is an alternative to TextBetween(). This is not the issue.

Even when using subfield(), I need to "hard-code" the substring that I am looking for. The issue is, if I take my above example, is that for apples, the subfield() function must first go fetch the first substring (green) and THEN go and fetch the 2nd substring (red).

The same goes for pears, it must first get substring (yellow), then 2nd (brown), then the 3rd (green).

Therefore, I am looking for a smart way of looping the values with the substring parameter. Hope this makes sense.

Thanks.

[tresesco]

See my updated post above. Hope that would help you.

Thanks.

[#5 · Accepted solution]

Hi Tresesco & Mohit,

Thanks a million. While I was looking for a crazy loop-in-loop, not hard-coding the substring returns all values. You guys are geniuses. Many thanks.

Kr

Cheenu

[#6]

Hi Tresesco,

Terribly sorry about not posting you as "Correct Answer" and giving you the points you deserve. I worked the strings from last to first and thought I could post two "Correct Answers".

My apologies.

Many thanks again.

Kr

Cheenu