# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
MVP

## Scripting or Chart

We have below purchase data in Brands vs Supplier format…

 Brand Supplier SUM(Purchase) B1 A 100 B1 B 50 B1 C 150 B1 D 180 B1 E 80 B2 B 250 B2 C 40 B2 D 60 B3 C 80 B3 D 100 B3 E 20 B4 C 60 B4 D 100 B4 E 180 B5 A 130 B5 C 250 B5 D 100 B5 E 85 B6 A 80 B6 B 100 B6 D 150

For each Brand, we have particular main (prime) supplier and rest suppliers are we using only if that Brand is not available from our major supplier.

So, I want below table…

Here Others are total for non-prime supplier.

How can I achieve this…

 Brand Supplier SUM(Purchase) B1 A 100 B1 C 150 B1 E 80 B1 Others 230 B2 B 250 B2 C 40 B2 Others 60 B3 C 80 B3 Others 120 B4 C 60 B4 D 100 B4 Others 180 B5 A 130 B5 D 100 B5 Others 335 B6 B 100 B6 Others 230
1 Solution

Accepted Solutions
Not applicable

## Re: Scripting or Chart

Hi Manish

You need to set up a table in the data that lists each Brand and their main supplier(s) (I am guessing from the above table that there could than 1?)

ie so something like

## Supplier_Type

B1

A

MAIN

B1

C

MAIN

B1

E

MAIN

B2

B

MAIN

B2

C

MAIN

Either join this to the main table or leave it floating, to be joined in the application.

Then set up the table chart as you did earlier, except do a calculated dimension for supplier:

Dimension1:Brand

Dimension2(calculated): =if(Supplier_Type='MAIN',Supplier,'Others')

Expression: =sum(purchase)

This will group any suppliers that are not the main one for that brand.

Erica

2 Replies
Not applicable

## Re: Scripting or Chart

Hi Manish

You need to set up a table in the data that lists each Brand and their main supplier(s) (I am guessing from the above table that there could than 1?)

ie so something like

## Supplier_Type

B1

A

MAIN

B1

C

MAIN

B1

E

MAIN

B2

B

MAIN

B2

C

MAIN

Either join this to the main table or leave it floating, to be joined in the application.

Then set up the table chart as you did earlier, except do a calculated dimension for supplier:

Dimension1:Brand

Dimension2(calculated): =if(Supplier_Type='MAIN',Supplier,'Others')

Expression: =sum(purchase)

This will group any suppliers that are not the main one for that brand.

Erica

Not applicable

## Re: Scripting or Chart

NB Manish, as an aside it could be easier (depending on your data model) to construct a table with every permutation of Brand and Supplier, with the Supplier name or "others" as a 3rd field:

Brand

Supplier

Supplier_Group

B1

A

A

B1

B

B

B1

C

OTHERS

B1

D

OTHERS

B2

A

OTHERS

B2

B

B

B2

C

C

B2

D

D

B3

A

A

This would be easier to apply in the chart, as you can add it straight in as a dimension. The downside is that it wont account for any new suppliers that are not yet in the list. So it depends on your data a lot really!

Erica