topic Re: Uniting field values based on another field in QlikView

Uniting field values based on another field

Wed, 10 Dec 2014 13:23:53 GMT

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

Re: Uniting field values based on another field

francoiscave — Wed, 10 Dec 2014 13:34:28 GMT

Hi Heidi,

Please see attached file.

François

Re: Uniting field values based on another field

mrybalko — Wed, 10 Dec 2014 13:34:50 GMT

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];

Re: Uniting field values based on another field

Anonymous — Wed, 10 Dec 2014 14:02:51 GMT

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

Re: Uniting field values based on another field

Wed, 10 Dec 2014 14:18:45 GMT

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

Re: Uniting field values based on another field

francoiscave — Wed, 10 Dec 2014 14:30:03 GMT

Heidi,

Use the concat() function with Distinct :

Data:

LOAD * Inline [

Site code,Market Segment

1,A

1,B

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]

;

Re: Uniting field values based on another field

Wed, 10 Dec 2014 14:41:15 GMT

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

Re: Uniting field values based on another field

francoiscave — Wed, 10 Dec 2014 15:06:36 GMT

It was a pleasure !

Take fun with QlikView !

François