Determine status

[agbcn]

Hi,

I've been struggling with a requirement and I can't find a proper solution.

Let's assume I have 2 tables Table A (work orders) and Table B (tasks related to work orders). Table A has a primary key (let's call it IdA) which is also a field in Table B. A work order in Table A will be closed once all its tasks are closed in Table B (status 0). So relationship between tables is 1..N (N could be 0) and status of a work order is given by the status of the tasks associated.

Table A

IdA        Description           New Calculated field

1            Order1                     Open

2            Order 2                    Closed

3            Order3                     Open

Table B

IdB       IdA         Status

1             1            0

2             1            1

3             2            0

4             2            0

5             2            0

6             3            1

7             3            1

Thanks in advance

Regards

Àlex

[RsQK · Accepted solution]

Hey, this should do it:

tempA:
LOAD * INLINE [
IdA,Description
1,Order 1
2,Order 2
3,Order 3
];

[Table B]:
LOAD * INLINE [
IdB,IdA,Status
1,1,0
2,1,1
3,2,0
4,2,0
5,2,0
6,3,1
7,3,1
];

[ml_open_closed]:
MAPPING LOAD * INLINE [
From,To
1,Open
0,Closed
];

Tmp:
LOAD
IdA,
Max(IdB) as IdB
RESIDENT [Table B]
GROUP BY IdA;

LEFT JOIN (Tmp)
LOAD
IdA,
IdB,
APPLYMAP('ml_open_closed',Status) as NewField
RESIDENT [Table B];

[ml_new_field]:
MAPPING LOAD
IdA,
NewField
RESIDENT Tmp;

DROP TABLE Tmp;

[Table A]:
LOAD *,
APPLYMAP('ml_new_field',IdA) as NewField
RESIDENT tempA;

DROP TABLE tempA;

[agbcn]

Thanks! It worked!

Regards

Àlex