topic Re: Concatenate strings for distinct values in QlikView

Concatenate strings for distinct values

Tue, 27 Jan 2015 08:03:57 GMT

Good morning, I have the following situation:

I have a code in column A and a column B with a field tha contains NOTES.
The field A may be repeated on multiple lines with different values of NOTES.

I need to have for each distinct value of the field A concatenation NOTES present in field B.

How can I do this?

Thanks

Re: Concatenate strings for distinct values

alexandros17 — Tue, 27 Jan 2015 08:06:29 GMT

Fields A and B are in the same table?

If the answer is yes then:

Load

...

A & B as myNewField

...

resident mytable;

let me know

Re: Concatenate strings for distinct values

MarcoWedel — Tue, 27 Jan 2015 08:10:24 GMT

Front end:

Chart with

Dimension: colA

Expression : concat(distinct colB)

Re: Concatenate strings for distinct values

Tue, 27 Jan 2015 08:17:43 GMT

Hi guys, sorry but I don't understand.

The fields A and B are in the same table.

The table is the following:

NOTE00F:

LOAD

NOKEY as [NUMERO CHIAVE],

NONOTA

...

Re: Concatenate strings for distinct values

anbu1984 — Tue, 27 Jan 2015 08:34:18 GMT

Can you post sample data and expected output

Re: Concatenate strings for distinct values

Tue, 27 Jan 2015 09:02:27 GMT

FIELD A (CODE)		FIELD B (NOTE)
LI11703020120117000000001		XXXXXXXXXX
LI11703020120117000000001		YYYYYYYYYY

LI11703020120314000000010		AAAAAAAAAA
LI11703020120314000000010		BBBBBBBBB
LI11703020120314000000010		CCCCCCCC

I expect that the results is:

LI11703020120117000000001	XXXXXXXXXXYYYYYYYYYY
LI11703020120314000000010	AAAAAAAAAABBBBBBBBBCCCCCCCC

Re: Concatenate strings for distinct values

anbu1984 — Tue, 27 Jan 2015 09:10:26 GMT

Load

FieldA,

Concat(FieldB,'')

From

Table

Group by FieldA

Re: Concatenate strings for distinct values

jonathandienst — Tue, 27 Jan 2015 09:12:24 GMT

That's what Marco's code should produce. With the correct field names this is (in a straight table):

Dimension: [NUMERO CHIAVE]

Expression: Concat(DISTINCT NONOTA)

HTH

Jonathan

Re: Concatenate strings for distinct values

jmmayoral3 — Tue, 27 Jan 2015 09:22:03 GMT

You can use this expression:

=aggr(concat(distinct FieldB,''),FieldA)

Re: Concatenate strings for distinct values

Wed, 28 Jan 2015 12:08:48 GMT

Hello guys, The concatenation should follow the order of the field C.

FIELD A (NOKEY)	FIELD B (NONOTA)	FIELD C ( RIGA)
LI11703020120117000000001	XXX	10
LI11703020120117000000001	YYY	20

LI11703020120314000000010	AAA	10
LI11703020120314000000010	BBB	20
LI11703020120314000000010	CCC	30

How do I give this indication to the function used? The function used is the following:
aggr (Concat (DISTINCT NONOTA, ''), NOKEY)

I would like to have the following string in output AAABBBCCC while I get the following string AAACCCBBB.

Thanks.

Re: Concatenate strings for distinct values

anbu1984 — Wed, 28 Jan 2015 16:23:40 GMT

aggr (Concat (DISTINCT NONOTA, '',RIGA), NOKEY)

Re: Concatenate strings for distinct values

Fri, 30 Jan 2015 10:14:14 GMT

Thanks Anbu, it works great !