topic Re: Creating multiple transactional lines from one line in QlikView

Creating multiple transactional lines from one line

Thu, 27 Jun 2013 09:46:18 GMT

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.

Re: Creating multiple transactional lines from one line

tresB — Thu, 27 Jun 2013 09:51:35 GMT

Use SUBFIELD() function in the script like

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

Thanks.

Re: Creating multiple transactional lines from one line

Thu, 27 Jun 2013 10:06:14 GMT

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.

Re: Creating multiple transactional lines from one line

tresB — Thu, 27 Jun 2013 10:08:13 GMT

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

Thanks.

Re: Creating multiple transactional lines from one line

Thu, 27 Jun 2013 10:27:41 GMT

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.

Cheenu

Re: Creating multiple transactional lines from one line

Thu, 27 Jun 2013 10:33:00 GMT

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.

Cheenu