Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Maybe.
TEST:
Load,
Product,
Cost,
if (Cost=CostA or Cost=CostB,'GroupX',
if( Cost=CostB,'GroupY',GroupZ' ) as Groups
....
Regards.
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.
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.
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
];