topic Re: Show total in QlikView

Show total

Sun, 02 Feb 2014 20:43:45 GMT

I have a table like:

Brand Product Sale

J&J Gillette 2

J&J Baby Oil 3

Pepsi Diet 1

Pepsi Max 2

Coke Diet 4

Coke Classic 5

I just want to show the J&J product data and Total J&J data so, In Edit Script I'm using:

if(Brand = 'J&J', Product, Brand) as Brand

which gives me the result

Brand Sales

Gillette 2

Baby Oil 3

Pepsi 3

Coke 9

Which is all good but how can I show Detail of J&J total? which in this case would be

Brand Sales

J&J 5

in addition to above.

Any help would be appreciated.

Thanks.

Re: Show total

Sun, 02 Feb 2014 21:10:22 GMT

Hi,

In your pivot chart you can use Brand as first dimension and if(Brand = 'J&J', Product, Brand) as a second dimension.

Can it fit what you want?

Re: Show total

Sun, 02 Feb 2014 21:33:30 GMT

Thanks for your response.

I know I can achieve that with pivot table but is it another way through scripting? The example I am giving is really basic but I want to use the idea.

Re: Show total

MK_QSL — Sun, 02 Feb 2014 21:41:41 GMT

Please check enclosed file.

Re: Show total

MK_QSL — Sun, 02 Feb 2014 21:47:04 GMT

if you are using Personal Edition... use below script....

Table1:

Load * Inline

[

Brand, Product, Sale

J&J, Gillette, 2

J&J, Baby Oil, 3

Pepsi, Diet, 1

Pepsi, Max, 2

Coke, Diet, 4

Coke, Classic, 5

];

NoConcatenate

Table2:

Load

Brand,

SUM(Sale) as TotalSale

Resident Table1

Where Brand = 'J&J'

Group By Brand;

Drop Table Table1;

Re: Show total

Sun, 02 Feb 2014 21:48:39 GMT

Thanks Manish for your response but I want to show all the other brand including the Product for J&J.

Brand Sales

Gillette 2

Baby Oil 3

Pepsi 3

Coke 9

J&J 5

Re: Show total

Sun, 02 Feb 2014 21:49:29 GMT

If J&J has only those 2 products, it's possible to keep script as it is and make a calculated dimension in your chart:

If(Match(Brand,'Gillette','Baby Oil'), 'J&J', Brand)

If there's far more than 2 products in J&J, keep the info through a second field in script. It can be a simple flag, like

If(Brand='J&J', 1, 0) as [flag J&J]

There are, of course, many other possible ways.

Re: Show total

MK_QSL — Sun, 02 Feb 2014 21:55:03 GMT

Table1:

Load * Inline

[

Brand, Product, Sale

J&J, Gillette, 2

J&J, Baby Oil, 3

Pepsi, Diet, 1

Pepsi, Max, 2

Coke, Diet, 4

Coke, Classic, 5

];

Join

Table2:

Load

Brand,

Product,

SUM(Sale) as TotalSale

Resident Table1

Group By Brand, Product;

Re: Show total

MK_QSL — Sun, 02 Feb 2014 21:55:53 GMT

Or This...

Table1:

Load * Inline

[

Brand, Product, Sale

J&J, Gillette, 2

J&J, Baby Oil, 3

Pepsi, Diet, 1

Pepsi, Max, 2

Coke, Diet, 4

Coke, Classic, 5

];

NoConcatenate

Table2:

Load

Brand,

Product,

SUM(Sale) as TotalSale

Resident Table1

Group By Brand, Product;

Drop Table Table1;

Re: Show total

Anonymous — Mon, 03 Feb 2014 08:26:01 GMT

I suppose you want this output in table:-

Brand Product Sale

Coke Diet 4

Coke Classic 5

J&J Gillette 2

J&J Baby Oil 3

J&J J&J 5

Pepsi Diet 1

Pepsi Max 2

Use the below script

Load * Inline
[
Brand, Product, Sale
J&J, Gillette, 2
J&J, Baby Oil, 3
Pepsi, Diet, 1
Pepsi, Max, 2
Coke, Diet, 4
Coke, Classic, 5
];

NoConcatenate

Table2:
Load
Brand, Brand as Product,
SUM(Sale) as Sale
Resident Table1
Where Brand = 'J&J'
Group By Brand;

Hope this meets ur requirements.