Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
J&J 5
in addition to above.
Any help would be appreciated.
Thanks.
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?
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.
Please check enclosed file.
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;
Thanks Manish for your response but I want to show all the other brand including the Product for J&J.
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.
Join
Product,
Group By Brand, Product;
Or This...
I suppose you want this output in table:-
J&J J&J 5
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];
Table2:Load Brand, Brand as Product, SUM(Sale) as Sale Resident Table1 Where Brand = 'J&J'Group By Brand;
Hope this meets ur requirements.
