Simple Table with Sub-totals

[Rich5678]

Using Qlik Sense, can you tell me, how to get Sub-totals within a Table.

For example, I have 2 Tables.

Table 1 (in CSV format)

Product,pType
Roses,Flowers
Jasmine,Flowers
Marigold,Flowers
Apples,Fruits
Bananas,Fruits
Grapes,Fruits
Peaches,Fruits
Walnuts,Nuts
Peanuts,Nuts

Table 2 (in CSV format):

Product,Quantity
Roses,20
Jasmine,200
Marigold,50
Apples,24
Bananas,48
Grapes,300
Peaches,36
Walnuts,12
Peanuts,25

Required:
Using above data, I want a Simple Table, where only 2 columns are shown, as below:

Product   Quantity
Roses        20
Jasmine   200
Marigold    50
Flowers     270
Apples        24
Bananas    48
Grapes      300
Peaches     36
Fruits         408
Walnuts     12
Peanuts      25
Nuts             37

I would like to see "Flowers", "Fruits" and "Nuts" in Bold Font

[rwunderlich · Accepted solution]

Is your Qlik Sense on-premise or Cloud?

The typical approach is to use a pivot table with a separate field for the pType, giving results like this:

Would that meet your needs?  If you absolutely need the two column approach you could combine the two csv files in the load script like this:

Data:
LOAD * Inline [
Product,pType
Roses,Flowers
Jasmine,Flowers
Marigold,Flowers
Apples,Fruits
Bananas,Fruits
Grapes,Fruits
Peaches,Fruits
Walnuts,Nuts
Peanuts,Nuts
]
;

Join (Data)
LOAD *, 0 as TotalRow Inline [
Product,Quantity
Roses,20
Jasmine,200
Marigold,50
Apples,24
Bananas,48
Grapes,300
Peaches,36
Walnuts,12
Peanuts,25
]
;

Concatenate (Data)
LOAD
1 as TotalRow,
pType,
pType as Product,
Sum(Quantity) as Quantity
Resident Data
Group By pType
;

Then use a standard table chart with dimensions:

Product 
Quantity

Sort Product column using "Sort by expression":

=FieldIndex('pType', pType)

I'm not sure how to bold a row in a Sense table, but you could mess with background or text color using an expression like:

=if(TotalRow, LightBlue())

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[vinieme12 · Accepted solution]

here is an alternative script, to only create multiple associations in Table1 only

Table1:
load Product,pType,Product as Product_dim,num(recno(),'#,##0.0') as Product_so inline [
Product,pType
Roses,Flowers
Jasmine,Flowers
Marigold,Flowers
Apples,Fruits
Bananas,Fruits
Grapes,Fruits
Peaches,Fruits
Walnuts,Nuts
Peanuts,Nuts
];
// NoConcatenate
temp_t:
Load pType,max(Product_so)+0.5 as Product_so
Resident Table1
Group by pType;

Left Join (temp_t)
Load Product,pType
Resident Table1;

Concatenate(Table1)
Load Product,pType,pType as Product_dim , Product_so
Resident temp_t;
drop table temp_t;

Table2:
load * inline [
Product,Quantity
Roses,20
Jasmine,200
Marigold,50
Apples,24
Bananas,48
Grapes,300
Peaches,36
Walnuts,12
Peanuts,25
];

Then in charts use Product_Dim instead

background expression

=if(frac(Product_so)=0.5,blue())