Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
What are you actually trying to achieve?
Andy
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
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;
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;
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
Hello,
No, it does not. And it has one missing ) at the end
Cheers,
Niko
Hi Manish,
Well this definitely does the trick. Why did I not figure this out ..
Cheers,
Niko
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()
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');