topic Re: Data grouping and creating filters in QlikView

Data grouping and creating filters

shan_sriv — Wed, 25 Nov 2020 16:16:04 GMT

Dear Experts,

I am very new to QlikView, I have a scenario could you please help me in getting it.

I have a table in that I have a column say Category and it has values like A,B,C,D but in front end I want to see them as A1, B2, C3, D4.

This renaming of values I managed to achieve by using following function:

if(match(Category,'A'),'A1',

if(match(Category,'B'),'B2',

if(match(Category,'C'),'C3',

if(match(Category,'D'),'D4',Category)))) as Category

Now I want to group these categories like Group 1 (A and B) & Group 2(C and D)

And in front end I will have 2 filters : Category & Group

which will be like :

Category Group

A1 Group 1

B2 Group 2

So now I want is when Group 1 is selected in filter A1 & B2 gets enabled and when Group 2 is selected C3 & D4 gets enabled and vice-versa.

Please help in achieving this.

Thanks,

Shan

Re: Data grouping and creating filters

trdandamudi — Sat, 11 Nov 2017 23:55:29 GMT

May be as below:

Data:

Load ID,

if(Category='A','A1',

if(Category='B','B2',

if(Category='C','C3',

if(Category='D','D4',Category)))) as Category,

If(Category='A' or Category='B','Group 1',

If(Category='C' or Category='D','Group 2')) as Group

;

Load * Inline [

ID,Category

1,A

2,B

3,C

4,D

];

Re: Data grouping and creating filters

maxgro — Sun, 12 Nov 2017 11:20:14 GMT

one option is to use the pick match

....

pick(match(Category, 'A', 'B', 'C', 'D'), 'A1', 'B2', 'C3', 'D4') as Category,

pick(match(Category, 'A', 'B', 'C', 'D'), 'Group 1', 'Group 1', 'Group 2', 'Group 2') as Group,

....

Which conditional functions do you use?

Re: Data grouping and creating filters

Or — Sun, 12 Nov 2017 13:29:30 GMT

In addition to Massimo's suggestion above, you could also use a hierarchy if you want:

Hierarchy in QlikView

Re: Data grouping and creating filters

MarcoWedel — Sun, 12 Nov 2017 21:54:23 GMT

Hi,

one example of a listbox displaying a drill down group that might suit your needs:

tabFilters:
LOAD * INLINE [
    Cat, Category, Group
    A, A1, Group1
    B, B2, Group1
    C, C3, Group2
    D, D4, Group2
];

table1:
LOAD RecNo() as ID,
    Chr(65+Floor(Rand()*5)) as Cat,
    Ceil(Rand()*100) as SomeFact
AutoGenerate 20;

hope this helps

regards

Marco

Re: Data grouping and creating filters

shan_sriv — Mon, 13 Nov 2017 07:24:32 GMT

Thanks a lot Thirumala for your response.

Re: Data grouping and creating filters

shan_sriv — Mon, 13 Nov 2017 07:26:00 GMT

Thanks a lot Massimo for you response, option provided by you is simple and works perfectly for me.

Re: Data grouping and creating filters

shan_sriv — Mon, 13 Nov 2017 07:28:06 GMT

Thanks Marco for your response. I got the solution from Massimo option. But sure I will try what you have explained, it will help me learning new options in QlikView.