Create hierarchy / grouping

[Anonymous]

Hi

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

A.

[Oleg_Troyansky]

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

[Anonymous]

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

[Anonymous]

Maybe.

TEST:

Load,

Product,

Cost,

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

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

....

Regards.

[Oleg_Troyansky]

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.

[Digvijay_Singh]

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.

[#7]

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

];