Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ericdelaqua
Creator
Creator

load sub totals as dimension from excel

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

 

ericdanqua_0-1643588956249.png

 

Labels (3)
1 Solution

Accepted Solutions
brunobertels
Master
Master

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;

View solution in original post

3 Replies
brunobertels
Master
Master

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;

vikasmahajan

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
)
Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
ericdelaqua
Creator
Creator
Author

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