Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Creating multiple transactional lines from one line

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:

TxnIDProductInfo
A1BananasYellow
A2ApplesGreen;Red
A3PearsYellow; Brown; Green

and I want to get this out of it:

TxnIDProductInfo
A1BananasYellow
A2ApplesGreen
A2ApplesRed
A3PearsYellow
A3PearsBrown
A3PearsGreen

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.

1 Solution

Accepted Solutions
Not applicable

Re: Creating multiple transactional lines from one line

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

View solution in original post

5 Replies
MVP
MVP

Re: Creating multiple transactional lines from one line

Use SUBFIELD() function in the script like

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

Cheers.

Thanks.

Not applicable

Re: Creating multiple transactional lines from one line

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.

MVP
MVP

Re: Creating multiple transactional lines from one line

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

Thanks.

Not applicable

Re: Creating multiple transactional lines from one line

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

View solution in original post

Not applicable

Re: Creating multiple transactional lines from one line

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