Joins

[dominic_marchan]

Hi all,

In my inventory table, I have models that have a child and others that do not. Inventory data is on the child and not the parent. What I would like as an end table is to see the minimum inventory of the childs linked back to the parent item.

I understand the first left join needed to get the parent-child relationship in the table, it's how to link the min value of the child to the parent that is causing me issues.

Table 1: inventory

Model	Inventory
A	10
B	15
C	0
C1	20
C2	25
D	30

table 2: parent-child relationship

Model	Child
C	C1
C	C2

Final table should be

Model	Inventory
A	10
B	15
C	20
D	30

[DavidM]

hi,

this requires couple of steps:

1. Rename inventory field, as you join on same column name and in inventory table model=child. You can do this in script (model as child), or in the source data.

2. Join the table

Left Join (inventory)

Load

Model,

Child

from Parent child

in model you will have now for C1 and C2 parent C, but those without children will be empty. So you reload the invetory table once again:

3. Load

If(Len(Trim(Model)=0,Child,Model) as Model

resident invetory

this condition gives value from Child field (previously Model) if the new Model field is empty (joined from parent-child table), if there is Valeu, ti will take that value.

Hope this helps

Edit: the final table will have three columns (you can drop the child column if you wish), and the C model will have 2 lines, but that should be ok once you use this values in frontend (as sum for example)

[StarinieriG]

Hi 

you could try in this way:

MAP_CHILD:
Mapping LOAD * Inline
[Model,Parent
C1,C
C2,C
];

TABLE:
LOAD
Model,
Max(Inventory) as Inventory
Group By
Model
;
LOAD
If(ApplyMap('MAP_CHILD',Model,0)=0,Model,ApplyMap('MAP_CHILD',Model,0)) AS Model,
Inventory
;
LOAD * Inline
[Model,Inventory
A,10
B,15
C,0
C1,20
C2,25
D,30
];