Uniting field values based on another field

[#1]

Hi!

I'm trying to create a new field based on two other fields. I have site codes that match to one, two/three Market Segments like this:

Site code      Market Segment

1                   A

1                   B

2                   A

3                   A

3                   B

3                   C

No I would like to create a united market segment field that unites the market segments per site code like this:

Site code      Market Segment       Market Segment New

1                   A                               A & B

1                   B                               A & B

2                   A                               A

3                   A                               A & B & C

3                   B                               A & B & C

3                   C                               A & B & C

How should this be done when there is thousands of rows to go through?

Thanks already!

Regards,

Heidi

[francoiscave]

Hi Heidi,

Please see attached file.

BR

François

[mrybalko]

Hello

Try the following code:

tmp:
Load
*
Inline [
Site code, Market Segment
1, A
1, B
2, A
3, A
3, B
3, C ];

LEFT JOIN(tmp)
LOAD
    [Site code],
    Concat([Market Segment], ' & ') as 'Market Segment New'
Resident tmp
Group by [Site code];

[Anonymous]

Hi Heidi,

Solution:

Table:

LOAD*Inline

[

Sitecode,MarketSegment

1,A

1,B

2,A

3,A

3,B

3,C

];

Left Join

LOAD

Sitecode,

Concat(MarketSegment,' & ') as [Market Segment New]

Resident Table

Group by Sitecode;

Output:

Regards

Neetha

[#5]

Hi!

This works otherwise well (thank you for that) expect one site code with same market segment can be listed many times. So at the moment the Market Segment New also has content like A & A & A &... How could this be fixed so that it would create only categories A, B, C, A&B, A&C, B&C and A&B&C?

Reg,

Heidi

[francoiscave · Accepted solution]

Heidi,

Use the concat() function with Distinct :

Data:

LOAD * Inline [

Site code,Market Segment

1,A

1,B

2,A

2,A

3,A

3,B

3,C

];

Left Join

Data_new:

LOAD

  [Site code],

  Concat(distinct [Market Segment],' & ') as [Market Segment New]

Resident Data

Group By

  [Site code]

  ;

[#7]

So simple but so hard Thank you for the quick help!

[francoiscave]

It was a pleasure !

Take fun with QlikView !

François