Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the data below and I am trying not to load the sub-totals but also retrieve the text after "Total" and make that a dimension instead. I will elt Qlik do the calculations but I need the grouping from the excel file.
how could this be achieved?
Data
Country | product | Sales |
US | TV | 500 |
US | TV | 500 |
US | Total Electronic | 1000 |
US | CHAIR | 200 |
US | COUCH | 350 |
US | PILLOW | 700 |
US | Total Funiture | 1250 |
Expected result
Country | Product group | Product | Sales |
US | Electronic | TV | 500 |
US | Electronic | TV | 500 |
US | Funiture | CHAIR | 200 |
US | Funiture | COUCH | 350 |
US | Funiture | PILLOW | 700 |
hi
may be
temp:
load
Country,
product,
Sales,
if(wildmatch(product,'Total*'),mid(product,6,),peek(Product_Group)) as Product_Group
from ...
final :
load *
resident temp where not wildmatch(Product,'Total*') ;
drop table temp;
hi
may be
temp:
load
Country,
product,
Sales,
if(wildmatch(product,'Total*'),mid(product,6,),peek(Product_Group)) as Product_Group
from ...
final :
load *
resident temp where not wildmatch(Product,'Total*') ;
drop table temp;
May be this :
Check the option to display totals for the desired dimension (in pivot tables) or measure (in straight tables).
For a custom totals row, use:
If(Dimensionality() = 1, 123, // Expression for regular rows 999 // Expression for totals )
For a custom totals column, use (only in pivot tables):
If(SecondaryDimensionality() = 1, 123, // Expression for regular columns 999 // Expression for totals )
@brunobertels your solution works perfectly for my use case. Thanks