topic Re: Concatenating rows based on certain conditions? in QlikView

How to concatenate rows based on certain conditions?

swarup_malli — Wed, 27 Mar 2013 19:26:44 GMT

Hi,

My requirement is to concatenate rows based on certain conditions.

There are 3 fields

The field WARRANTY_CLAIM contains numbers , TEXT_NOTE field contains comments and the filed NOTE_SEQ indicates sequence.

The problem here is the field Text_NOTE holds comments, the comments are distributed over multiple rows.

What i mean by that is (see the attached file) the comment " Some issue with the component ,it broke and has to be replaced by the end of next month" has been broken down and distributed in 3 rows.

Currently the values in the field NOTE_SEQ 1305 indicates the start of comment,1306 indicates next comment followed by 1307 so on which ends at 1312.

Similarly there are comments that start at 1205..may go till 1212

some times a comment could fit in one row itself ..so it will have just a starting value of 1205 or 1305 (1205 or 1305 or 1605 indicate the start of comments)

followed by a row with a different warranty claim number which may have the value 1605 under the field NOTE_SEQ.

I'm trying to concatenate all the rows ..which should look like (see below)

Re: Concatenating rows based on certain conditions?

Wed, 27 Mar 2013 20:57:27 GMT

See attached.

Re: Concatenating rows based on certain conditions?

Wed, 27 Mar 2013 21:05:49 GMT

Here is the file with the concatenated NOTE_SEQ field included. I noticed you had that in your question, but forgot to include it.

Re: Concatenating rows based on certain conditions?

swarup_malli — Thu, 28 Mar 2013 14:36:12 GMT

Rebecca,

Very impressive the way you did it. Its very close to what I wanted .

Is it possible to display 1205 ( see fig above) as a separate record ( I have circled it in red in the fig above) for record 1200590.(note 1200590 should appear twice in the table one record for 1205 series and one for 1305 series...so on )

My requirement is values starting 1205..to 12012 in NOTE_SEQ must appear as one record for claim number 1200590 (circled in green)

and values starting 1305..to 13012 in NOTE_SEQ must appear as another record for claim number 1200590 (circled in green)

Is it possible to make 1200590 appear twice in the table ie one record for 1205 and the other for concatenated 1305,1306,1307.(note in the snap I have attached above I have circled the number in red)

Thank you

Swarup

Re: Concatenating rows based on certain conditions?

Anonymous — Thu, 28 Mar 2013 16:56:40 GMT

Try this link

http://community.qlik.com/message/184593#184593

Re: Concatenating rows based on certain conditions?

Thu, 28 Mar 2013 18:49:29 GMT

That definitely makes it more complicated. I am not sure how to achieve that, but I can play around with it. It may be above my head though!!

Re: Concatenating rows based on certain conditions?

swarup_malli — Thu, 28 Mar 2013 19:01:53 GMT

Thank you Rebecca

Re: Concatenating rows based on certain conditions?

swarup_malli — Fri, 05 Apr 2013 15:08:40 GMT

I was able to find a work around.

By creating a separate qvd for 1205..series ,one for 1305..series ,so on.

Thank you for the logic Rebbeca, It really solved the issue

Regards

Re: Concatenating rows based on certain conditions?

Fri, 05 Apr 2013 15:17:48 GMT

I am glad you figured it out! I played around with it for a little while with no luck and then got slammed at work...