topic Re: How to concatenate and enumerated table in QlikView

How to concatenate and enumerated table

Mon, 18 Jul 2016 21:12:12 GMT

Hi all!

I have a table that has 3 variables Text_ID | Text Line| Text

In our system if there are over X number of characters it adds a new row to the data table. So if the field said:

Hello World

My Name is Robyn

How are you?

It would translate to the data table as

1111 | 1 | Hello World

1111 | 2 | My Name is Robyn

1111 | 3 | How are you?

I need the output to concatenate all of these into one cell for Text_ID 1111. How do I do it when I don't have a defined number of Text Lines?

Re: How to concatenate and enumerated table

swuehl — Mon, 18 Jul 2016 21:31:25 GMT

Try something like

LOAD Text_ID,

Concat( Text, '', [Text Line]) as NewText

RESIDENT YourTable

GROUP BY Text_ID;

Re: How to concatenate and enumerated table

Mon, 18 Jul 2016 21:41:35 GMT

Forgive me for being VERY green..

If i'm bringing the data in from a QVD it looks like this

TextTable:

LOAD:

TextID,

TextLine,

Text

FROM

File Location

How would I change this to do what you are suggesting?

Re: How to concatenate and enumerated table

rwunderlich — Mon, 18 Jul 2016 23:39:52 GMT

Replace the datasource (Resident) with your QVD source:

LOAD Text_ID,

Concat( Text, '', [Text Line]) as NewText

FROM yourqvd.qvd (qvd)

GROUP BY Text_ID;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: How to concatenate and enumerated table

Tue, 19 Jul 2016 13:39:33 GMT

I must be doing something wrong. I'm still getting an error with this..

OrderText:

LOAD
T$CTXT As TextID,
T$SEQE As TextLine,
T$TEXT As Text,
Concat( T$TEXT,'', T$SEQE) as NewText

FROM
$(vPathRoot)\QVD\ORA_BAAN\TTTTXT010700_Text_Table.qvd
(qvd)

GROUP BY T$CTXT;

Re: How to concatenate and enumerated table

Tue, 19 Jul 2016 13:42:50 GMT

Fixed it! Thank you!!!

Re: How to concatenate and enumerated table

swuehl — Tue, 19 Jul 2016 15:53:57 GMT

Remove T$SEQE and T$TEXT as stand-alone (not aggregated, qualifying) fields from your load.

LOAD
T$CTXT As TextID,
T$SEQE As TextLine,
T$TEXT As Text,
Concat( T$TEXT,'', T$SEQE) as NewText

FROM
$(vPathRoot)\QVD\ORA_BAAN\TTTTXT010700_Text_Table.qvd
(qvd)

GROUP BY T$CTXT;