Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Show total

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.

9 Replies
Not applicable

Re: Show total

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?

Not applicable

Re: Show total

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.

MVP
MVP

Re: Show total

Please check enclosed file.

MVP
MVP

Re: Show total

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;

Not applicable

Re: Show total

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

Not applicable

Re: Show total

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.

MVP
MVP

Re: Show total

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;

MVP
MVP

Re: Show total

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;

shekharnil
New Contributor III

Re: Show total

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.