topic Re: load sub totals as dimension from excel in App Development

load sub totals as dimension from excel

ericdelaqua — Mon, 31 Jan 2022 00:29:45 GMT

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

Re: load sub totals as dimension from excel

brunobertels — Mon, 31 Jan 2022 09:12:39 GMT

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;

Re: load sub totals as dimension from excel

vikasmahajan — Mon, 31 Jan 2022 13:52:23 GMT

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
)

Re: load sub totals as dimension from excel

ericdelaqua — Mon, 31 Jan 2022 17:14:52 GMT

@brunobertels your solution works perfectly for my use case. Thanks