I have two tables:
Orders:
Order_ID
Order_DT
Ship_ID
Ship_DT
Prod_ID
ProdExP_DT
O1
2022-01-01
S1
2022-01-01
P1
2022-01-30
O1
2022-01-01
S1
2022-01-01
P2
2022-02-28
O2
2022-01-02
S2
2022-01-02
P3
2022-03-01
O3
2022-01-03
S3
2022-01-03
P4
2022-03-04
Products:
Prod_ID
HasMembers
ProdEP_DT
P1
S1
2022-01-30
P1
O1
2022-01-30
P1
P2
2022-01-30
P2
S1
2022-02-28
P2
O1
2022-02-28
P2
P1
2022-02-28
P3
S2
2022-03-01
P4
S3
2022-03-04
P4
O3
2022-03-04
I want to check if the table has orders, ships and products mapped.
So, I want Orders_Map be
Order_ID
Order_DT
Ship_ID
Ship_DT
Prod_ID
ProdEP_DT
Member_Set
O1
2022-01-01
S1
2022-01-01
P1
2022-01-30
(O1,S1,P1,P2)
O1
2022-01-01
S1
2022-01-01
P2
2022-02-28
(O1,S1,P1,P2)
O2
2022-01-02
S2
2022-01-02
P3
2022-03-01
(O2,S2,P3)
O3
2022-01-03
S3
2022-01-03
P4
2022-03-04
(O3,S3,P4)
And, Products_Mapped be
Prod_ID
HasMembers
ProdEP_DT
Member_Set
Map_IND
P1
S1
2022-01-30
(O1,S1,P1,P2)
Y
P1
O1
2022-01-30
(O1,S1,P1,P2)
Y
P1
P2
2022-01-30
(O1,S1,P1,P2)
Y
P2
S1
2022-02-28
(O1,S1,P1,P2)
Y
P2
O1
2022-02-28
(O1,S1,P1,P2)
Y
P2
P1
2022-02-28
(O1,S1,P1,P2)
Y
P3
S2
2022-03-01
(S2,P3)
N
P4
S3
2022-03-01
(O3,S4,P4)
Y
P4
O3
2022-03-04
(O3,S4,P4)
Y
How do I write the column calculation for member_set in Orders and Products table, and Map_IND column for products table?
The original data has 100 thousands data, I tried lookup function, and failed T.T
Thanks!
... View more