Qlik Community

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
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

Brand

Supplier

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

Brand

Supplier

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

Community Browser