Comparison between two datasubsets

Question

Hi, 
 Suppose I have two datasets "A" & "B". I want to compare both datasets based on property_id and merged it into another variable called "C". Conditions are as follow: 
 
 If the value of property_id appears in both the datasets are common then user_id of "B" will be assigned to "A's" user_id 
 if the property_id of "A" does not exists in "B" than nothing happens 
 if the property_id of "B" does not exists in "A" than make active to FALSE and append 
 
 Details are below: 
 A: { 
 { user_id :null, property_id:2, active: true}, 
 {user_id : null, property_id:4, active: true } 
 {user_id :null, property_id:7, active: true} 
 ) 
 
 B: { 
 { user_id :100, property_id:1, active: true}, 
 {user_id : 101, property_id:2, active: true } 
 {user_id :102, property_id:6, active: true} 
 ) 
 
 Final Output let "C": 
 C : { 
 {user_id :100, property_id:1, active: FALSE}, /*active is FALSE as this is not exists in "A"*/ 
 { user_id : 101, property_id:2, active: true}, /*This property_id exists in both datasets "A" & "B" hence value of user_id in "A" will be replaced by "B"s user_id value*/ 
 {user_id : null, property_id:4, active: true } /* Nothing happens hence this is new value in "A" */ 
 {user_id :null, property_id:7, active: true} /* Nothing happens hence this is new value in "A" */ 
 {user_id :102, property_id:6, active: FALSE}, /*active is FALSE as this is not exists in "A"*/ 
 } 
 
 I want store this final output in our CDT for further database operation. Appreciate your help in this regards/ 
 Thanks

Krishna Chaitanya · Accepted Answer

load(
 local!A: {
 {
 user_id: null,
 property_id: 2,
 active: true
 },
 {
 user_id: null,
 property_id: 4,
 active: true
 },
 {
 user_id: null,
 property_id: 7,
 active: true
 }
 },
 local!B: {
 {
 user_id: 100,
 property_id: 1,
 active: true
 },
 {
 user_id: 101,
 property_id: 2,
 active: true
 },
 {
 user_id: 102,
 property_id: 6,
 active: true
 }
 },
 {
 index(
 local!A,
 wherecontains(
 difference(
 local!A.property_id,
 local!B.property_id
 ),
 local!A.property_id
 ),
 null
 ),
 a!forEach(
 items: index(
 local!B,
 wherecontains(
 difference(
 local!B.property_id,
 local!A.property_id
 ),
 local!B.property_id
 ),
 null
 ),
 expression: {
 user_id: fv!item.user_id,
 property_id: fv!item.property_id,
 active: false()
 }
 ),
 a!forEach(
 items: index(
 local!B,
 wherecontains(
 intersection(
 local!B.property_id,
 local!A.property_id
 ),
 local!B.property_id
 ),
 null
 ),
 expression: {
 user_id: fv!item.user_id,
 property_id: fv!item.property_id,
 active: true()
 }
 )
 }
)

Comparison between two datasubsets

Top Replies