Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
agbcn
Contributor III
Contributor III

Determine status

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

1 Solution

Accepted Solutions
RsQK
Creator II
Creator II

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;

View solution in original post

2 Replies
RsQK
Creator II
Creator II

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
Contributor III
Contributor III
Author

Thanks! It worked!

Regards

Àlex