topic Re: How to group values inside field? in QlikView

How to group values inside field?

Thu, 28 Apr 2016 08:08:36 GMT

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

Re: How to group values inside field?

awhitfield — Thu, 28 Apr 2016 08:13:32 GMT

What are you actually trying to achieve?

Andy

Re: How to group values inside field?

Thu, 28 Apr 2016 08:16:52 GMT

That I had field values

K1,K2,K4,

K1,

K2,

K3,

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

Re: How to group values inside field?

MK_QSL — Thu, 28 Apr 2016 08:26:07 GMT

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;

Re: How to group values inside field?

MK_QSL — Thu, 28 Apr 2016 08:29:30 GMT

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;

Re: How to group values inside field?

Thu, 28 Apr 2016 08:34:42 GMT

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

Re: How to group values inside field?

Thu, 28 Apr 2016 08:37:27 GMT

Hello,

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

Cheers,

Niko

Re: How to group values inside field?

Thu, 28 Apr 2016 08:41:06 GMT

Hi Manish,

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

Cheers,

Niko

Re: How to group values inside field?

tresB — Thu, 28 Apr 2016 08:43:15 GMT

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()

Re: How to group values inside field?

Thu, 28 Apr 2016 08:48:04 GMT

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

Re: How to group values inside field?

tresB — Thu, 28 Apr 2016 08:51:12 GMT

You are right! I just copied and pasted it. Additional else parts are redundant there. Now stroke off.

Re: How to group values inside field?

Thu, 28 Apr 2016 08:52:53 GMT

But there is only one problem, this does not show inside a pivot-table for some reason o.O

Cheers,

Niko

Re: How to group values inside field?

tresB — Thu, 28 Apr 2016 08:54:57 GMT

It should, if you use aggregation function like Sum(Value1) as expression.

Re: How to group values inside field?

Thu, 28 Apr 2016 09:59:32 GMT

Hi,

Could you please open a little bit, what are you doing on that concatenate part of this script :)?

It would be very nice!

Cheers,

Niko

Re: How to group values inside field?

MK_QSL — Thu, 28 Apr 2016 10:53:10 GMT

You can use below one as well

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

Order By part,place

;

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

This will Concat Distinct place and separate them by |

the last parameter place will make sure that the concatenation will happens in order.. i.e. even if K4, K2 and K1 is the right order, they will concatenate as K1, K2 and K4

Now consider that you have only K1 and K2 available for any part but you want only parts with K1, K2 and K4 then you can use below one.. otherwise you can ignore below line.

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