How to get a table like this

[yanzhao_LBS]

I have 3 tables

1.

2.

3.

and I want a table to display all the information detail like this:

but it seems that 

it's ok to display the sum of them or one is sum the other is detail  ,but not ok for the both details

sum of them:

one is sum the other is detail:

not ok for the both details:

is that impossible to display both detail? or how to display them in one visual?

[ryan_mayu · Accepted solution]

It looks like the expected output only need the data from table 2 and 3.

You can create index and custom columns in PQ for both table, then merge two tables.

pls see the attachment below

[yanzhao_LBS]

another question ,if i don't know which type has the larger number of record,(in this case,request count is bigger than consume,so i can merge like this way) if I dont know which one has the bigger number or it changes all the time ,does it work also?

[ryan_mayu]

this solution doesnot conisider the number of rquest and consum qty. 

It only depends on the rows of each product code. 

[yanzhao_LBS]

but if i merge like this (conusmed is on the top)

the result will be like this 

[ryan_mayu]

@yanzhao_LBS 

when you are merging the tables, press ctrl and select product code and custom columns. You will see the numbers beside the column name. Make sure the numbers are matching in both tables. 

[yanzhao_LBS]

ok.yes.that works.

[yanzhao_LBS]

i followed you step ,but get different result,is there anything need to set in options and settings?

[Ashish_Mathur]

Hi,

In the Fourth table, why are cells D3 and D7 left blank?  Shouldn't they be 30 and 20?

[yanzhao_LBS]

as Code001 and Code003 both only have 1 consumed record.