Forum Discussion

jessicarocha's avatar
jessicarocha
Icon for Helper IV rankHelper IV
2 years ago

Create calculated column on a table based on columns from two different other tables

I would like to create a column in one table that is based in two other tables. The desired column (placed in the UNION table) should be able to say the amount of customer order schedule lines and purchee order schedule lines (CUSTO and PURCO).

 

Customer order schedule line table (CUSTO): 

 

Doc_nr Item_nr Schedule_line_nr Order_qty  Confirmed_qty Delivery_date
0002658769 0010 001 10 10 01.04.24
0002658769 0020 001 50 45 01.04.24
0002658769 0020 002 0 5 05.04.24

0002185398

 

0010 001 100 

60

 

10.04.24
0002185398 0010 002 0 20 14.04.24
0002185398 0010 003 0 20 20.04.24

 

Purchase order schedule line (PURCO): 

 

Doc_nr Item_nr Schedule_line_nr Order_qty  Confirmed_qty Delivery_date
0004598731 0010 001 30 0 04.04.24
0004598731 0010 002 0 30 11.04.24
0004592147 0010 001 45 45 05.04.24

 

Header of the documents (UNION):

Doc_nr Item_nr Order_qty Conf_qty Source_table Desired column (Schedule line amount)
0002658769 0010 10 10 CUSTO 1
0002658769 0020 50 50 CUSTO 2

0002185398

 

0010 100 100 CUSTO 3

0004598731

 

0010 30 30 PURCO 2

0004592147

 

0010 45 45 PURCO 1

 

Relationships: 

- PURCO n:1 UNION

- CUSTO n:1 UNION

 

I tried formulas for a calculated column with SUMMARIZE or LOOKUPVALUE but it is not working. 

One example of what I tried: 

 

 

 

 

TEST = 
IF(
   UNION[Source_table) = "PURCO", 
   SUMMARIZE(PURCO, PURCO[Doc_nr], PURCO[Item_nr], "amount sched. line", COUNT(PURCO[Schedule_line_nr]), 
   SUMMARIZE(CUSTO, CUSTO[Doc_nr], CUSTO[Item_nr], "amount sched. line", COUNT(CUSTO[Schedule_line_nr])
)

 

 

 

 

Can you help me? Thank you very much! 

Need Help

1 Reply