concatenating specific rows together

[mh_elhassan]

Hello community,
I am trying to concatenate row together but i am finding some diffuculty. I tried a loop but i am missin somtheting. below is a sample data

id	flag	message	indexing
c1	a	ggg	1
c1		hello	1
c1		done	1
c1	b	text tetx	2
c1		answer	2
c1	C	questionquestion	3
c1		answer	3
c1		b	3
c1		v	3
c1		c	3
c2	a	text3	4
c2		text4	4
c2		tex5	4
c2	b	tex6	5
c2		tex7	5

i need 1st to give an index as previwed above then i need to concatenate all rows that are under a certain flag. when the flag is not empty, it means a question, and when it is empty it means asnswers for that question. so i need to group the answers together.

if anybody can help i would rreally appreciate

[iCFO]

If the goal is to put the answers in separate columns on the same line as the question, then you could always use a row splitter to separate out the rows with an empty Flag, then use the Joiner node to Join them back together by the Index number.

[mh_elhassan]

Still i will need to concatenate the awnsers to its specific question whis also why i need to index them to performe the join later

[MartinDDDD]

I think this can work for you:

1. Split Qs and As by missing value
2. Process As through group loop to add an “AnswerNo” column
3. Pivot the As: Group => Indexing, Pivot => AnswerNo, Aggregate => message (First) => by adding the running AnswerNo it is ensured that we are not missing anything
4. Join both datasets on indexing column

Here’s the prototype:

concatQA.knwf (84.4 KB)

[takbb]

Hi @mh_elhassan , are you also asking how to actually create the index column that you have shown? It’s not clear to me if that’s what you want, and I think the answers above generally assume you already have that indexing column… Is that part of the problem?

If so, the creation of the indexing column can be done by using a Rule Engine followed by a Moving Aggregation node as described in the following post

Get latest "batch" from table

Hi @tb_g_23 , I’ve had a re-think and a re-read. I hadn’t spotted the additional features of your data, and I generally find it easier to analyse actual sample data rather than screenshots. OK, so if we are to assume that every batch is marked by having an initial activity_input_order of 0, and that they are listed in ascending order, so that they batches are always grouped together in your data, with you data in id order, then this becomes a case of marking the batches, and a fairly co…

[PBJ]

3 first nodes to create index.

From missing value node to Column renamer, how to create row of questions. You can adapt to your needs.

Best regards.

KNIME_Elhassan.knwf (84,4 Ko)

[mh_elhassan]

thanks @takbb @PBJ @MartinDDDD @iCFO for your effort
as iCFO answered i need the questions to be in a column and the answers for these questions to be in another single column.each answers with their one question.
there is one question then several lines of answers for that question. i need these lines to be combined in one instead of 2 or 6 rows.
below is the sample output

conversation num	Question num	answers
c1	1	ggg. hello. done
c1	2	text tetx. answer.
c1	3	questionquestion. answer. b.v.c

[PBJ]

KNIME_Elhassan_v2.knwf (83,2 Ko)

[mh_elhassan]

@PBJ thanks… much appreciated