Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Highlighted
MVP
MVP

Re: How to group values inside field?

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
Partner
Partner

Re: How to group values inside field?

What are you actually trying to achieve?

Andy

Not applicable

Re: How to group values inside field?

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

MVP
MVP

Re: How to group values inside field?

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;

Highlighted
MVP
MVP

Re: How to group values inside field?

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

Not applicable

Re: How to group values inside field?

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

Re: How to group values inside field?

Hello,

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

Cheers,

Niko

Not applicable

Re: How to group values inside field?

Hi Manish,

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

Cheers,

Niko

MVP
MVP

Re: How to group values inside field?

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

Re: How to group values inside field?

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');