Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to group values inside field?

Hello,

I am trying to group my data in QlikView. And it is not going well.

I am using the following script:

if(place='K1' or place='K2' or place='K4', 'K1,K2,K4',
if(place='K1', 'K1',
if(place='K2', 'K2',
if(place='K3', 'K3',
if(place='K4', 'K4',
))))) as test

but all I get out, is the first field, that combines the three fields.. But it does not give me the separate values at all.

I have attached a workbook also to this message.

Any ideas how to approach this ?

I am trying to show these values in a pivot-table.

Cheers,

Niko

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Or in case if some part don't have all K1, K2 and K4, and you want to ignore 2 combination, use below..

DATA:

LOAD * Inline

[

part, place, value1, value2

A, K1, 10, 120,

A, K2, 20, 110,

A, K3, 30, 100,

A, K4, 40, 90,

B, K1, 50, 80,

B, K2, 60, 70,

B, K3, 70, 60,

B, K4, 80, 50,

C, K1, 90, 40,

C, K2, 100, 30,

C, K3, 110, 20,

];

Concatenate

Load part, Replace(place,'|',', ') as place, value1, value2 Where place = 'K1|K2|K4';

Load Distinct

  part,

  Concat(place,'|',place) as place,

  Sum(value1) as value1,

  Sum(value2) as value2

Resident DATA

Where Match(place,'K1','K2','K4')

Group by part;

View solution in original post

14 Replies
awhitfield
Partner - Champion
Partner - Champion

What are you actually trying to achieve?

Andy

Not applicable
Author

That I had field values

K1,K2,K4,

K1,

K2,

K3,

K4

So I could show tha values for this first group and individual parts in a pivot table.

So it would be something like

K1,K2,K4     70

K1     10

K2     20

K3     30

K4     40

Cheers,

Niko

MK_QSL
MVP
MVP

DATA:

LOAD * Inline

[

part, place, value1, value2

A, K1, 10, 120,

A, K2, 20, 110,

A, K3, 30, 100,

A, K4, 40, 90,

B, K1, 50, 80,

B, K2, 60, 70,

B, K3, 70, 60,

B, K4, 80, 50,

C, K1, 90, 40,

C, K2, 100, 30,

C, K3, 110, 20,

C, K4, 120, 10

];

Concatenate

Load Distinct

  part,

  Concat(place,', ',place) as place,

  Sum(value1) as value1,

  Sum(value2) as value2

Resident DATA

Where Match(place,'K1','K2','K4')

Group by part;

MK_QSL
MVP
MVP

Or in case if some part don't have all K1, K2 and K4, and you want to ignore 2 combination, use below..

DATA:

LOAD * Inline

[

part, place, value1, value2

A, K1, 10, 120,

A, K2, 20, 110,

A, K3, 30, 100,

A, K4, 40, 90,

B, K1, 50, 80,

B, K2, 60, 70,

B, K3, 70, 60,

B, K4, 80, 50,

C, K1, 90, 40,

C, K2, 100, 30,

C, K3, 110, 20,

];

Concatenate

Load part, Replace(place,'|',', ') as place, value1, value2 Where place = 'K1|K2|K4';

Load Distinct

  part,

  Concat(place,'|',place) as place,

  Sum(value1) as value1,

  Sum(value2) as value2

Resident DATA

Where Match(place,'K1','K2','K4')

Group by part;

Not applicable
Author

Hi Niko,

Can you please try below and let me know if it is working.

if(place='K1', 'K1',

if(place='K2', 'K2',

if(place='K3', 'K3',

if(place='K1,K2,K3', 'K4',

)))

Thanks,
Sreeman

Not applicable
Author

Hello,

No, it does not. And it has one missing ) at the end

Cheers,

Niko

Not applicable
Author

Hi Manish,

Well this definitely does the trick. Why did I not figure this out ..

Cheers,

Niko

tresesco
MVP
MVP

May be like in attached qvw.

DATA:

LOAD * Inline

[

part, place, value1, value2

A, K1, 10, 120,

A, K2, 20, 110,

A, K3, 30, 100,

A, K4, 40, 90,

B, K1, 50, 80,

B, K2, 60, 70,

B, K3, 70, 60,

B, K4, 80, 50,

C, K1, 90, 40,

C, K2, 100, 30,

C, K3, 110, 20,

C, K4, 120, 10

];

load

     part,

     value1,

     value2,

  if(place='K1' or place='K2' or place='K4', 'K1,K2,K4',

  if(place='K1', 'K1',

  if(place='K2', 'K2',

  if(place='K4', 'K4',

  )))) as place

Resident DATA where Match (place,'K1', 'K2', 'K4');

//drop table DATA;



Then in the front end you might use Sum()

Not applicable
Author

This works as well. Maybe the second load could only be

data2:

Concatenate

load

part,

value1,

value2,

  if(place='K1' or place='K2' or place='K4', 'K1,K2,K4',

  ) as place

Resident DATA where Match (place,'K1', 'K2', 'K4');