topic Create hierarchy / grouping in QlikView

Create hierarchy / grouping

Anonymous — Tue, 13 Oct 2015 14:37:18 GMT

I have separate fields showing different costs all with values

CostA

CostB

CostC

CostD

CostE

CostF

I want to be able to create a level above that to group the costs together in a certain way e.g.

GroupX contains CostA, CostC

GroupY contains CostB

GroupZ contains CostD, CostE & CostF

What is the best way to do it?

Thanks

Re: Create hierarchy / grouping

Oleg_Troyansky — Tue, 13 Oct 2015 14:56:39 GMT

The answer depends on your data structure:

If your 6 fields are stored in the same table, as 6 different fields iterated over multiple rows, then perhaps the "grouped" costs could also be stored as extra fields in the same table:

LOAD

Product,

CostA,

CostB,

CostC,

CostD,

CostE,

CostF,

CostA + CostC as GroupX,

CostB as GroupY,

...

On the other hand, if your 6 costs are stored in a "normalized" fashion, qualified by a code - something like this:

Product,

CostType,

CostAmount

Then you could create another field CostTypeGroup and associate group X with types A and C, etc... Depending on your analytical needs, one solution can work better than the other.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Create hierarchy / grouping

Anonymous — Tue, 13 Oct 2015 16:13:40 GMT

Your first example would work, but I have dozens of different charges and the groupings may change. I've got the groupings on a spreadsheet as follows

Charge	Group
CostA	GroupX
CostB	GroupY
CostC	GroupX
CostD	GroupZ
CostE	GroupZ
CostF	GroupZ

but I'm not sure how this would link to the fieldnames in the data

Re: Create hierarchy / grouping

Anonymous — Tue, 13 Oct 2015 16:21:24 GMT

Maybe.

TEST:

Load,

Product,

Cost,

if (Cost=CostA or Cost=CostB,'GroupX',

if( Cost=CostB,'GroupY',GroupZ' ) as Groups

....

Regards.

Re: Create hierarchy / grouping

Oleg_Troyansky — Tue, 13 Oct 2015 16:23:44 GMT

If you have dozens of different charges - does it mean that you have dozens of fields describing the different costs? Not very convenient to work with, is it?

I'd recommend normalizing your data into the second structure that I suggested and to use dynamic groupings with it.

Re: Create hierarchy / grouping

Digvijay_Singh — Tue, 13 Oct 2015 17:56:29 GMT

Your data is appearing like a cross table, I guess once we change it to the proper form using Cross table keyword while loading, group categorization would be very easy.

Can you share the dimensions you have and sample data rows to confirm the same.

Re: Create hierarchy / grouping

Wed, 14 Oct 2015 07:26:11 GMT

Hi all

Maybe load inline table will work

as follow

LOAD * INLINE [

Cost, Cost Group

CostA, Group X

CostB, Group Y

CostC, Group X

CostD, Group Z

CostE, Group Z

CostF, Group Z

];