how to create this chart

[jduluc12]

I have two tables

First table:

customer       Item

cust1              item1

cust1               item2

cust2               Item1

cust2                item4

Second Table:

item      name     qty

item1     cust1     12

item1     cust3     14

item1     cust2     15

item2     cust5     10

item2     cust1     11

they are linked on item

now i need the following chart

customer     item     qty

cust1          item1     12

cust1          item2      11

cust2          item1     15

cust2          item4     0

any suggestion on the expression for the qty?

[shraddha_g]

Why do you need 1st table when you can alias name field as Customer in 2nd table?

[jduluc12]

both are coming from different data source and and there are some more fields in the first table that i need to take on chart.

i removed them to simplify the problem.

[shraddha_g]

In Simple table

DImension1 : Customer

Dimension 2: Item

Measure : sum(Qty)

[jduluc12]

that does not work...

because

it brings data like this

Customer     Item     sum(qty)

cust1          item1       12+14+15

and henceforth..

[shraddha_g]

when linking 2 tables ,make composite key of Item and Customer

[Anonymous · Accepted solution]

Hi,

This will happen since you have linked tables only on basis of 'Item" field.

Is there any other key field to link the tables? Else you can link it on basis of Item & Customer

First table:

Load customer,

       Item,

       customer & Item as Key

From....;

Second Table:

Load

      item,

     name,

     qty,

     name & Item as Key

From...;

[jduluc12]

This is what i am trying.

[Anonymous]

Hi Jean,

Did it work?

[jduluc12]

Yes It did.

Thanks!