Combine two datasets

I have two sets of data (same schema):

Account Id, Name, Status, EngineType

One data set has 5 recs --> accountId = 100, 101, 102, 103, 104 but Engine type is blank

ID      Name        Status      Engine Type

100    Name1      OK            <null>

101     Name2      OK            <null>

102     Name3      Fail           <null>

103     Name4      OK            <null>

104     Name5      OK            <null>

Second data set has two records:  AccountId 102, enginetype: Jet, AccountId: 103, engine type: Gasoline

ID      Name        Status      Engine Type

102    Name2      Fail           Jet

103    Name3      OK            Gasoline

The final data should have five records:

AccountId 100, 101, 104 will have enginetype = blank and accointid 102, - enginetype: Jet, Accountid 103 - engine type:Gasoline

ID      Name        Status      Engine Type

100    Name1      OK            <null>

101     Name2      OK            <null>

102     Name3      Fail           Jet

103     Name4      OK            Gasoline

104     Name5      OK            <null>

Seems staright forward but not working for me.

1 Comment

I tried nested iterate loop.  Inner loop has 5 record but enginetype blank and acumulating.  Outer loop has two records AccountId 102, enginetype: Jet, Accountid 103, enginetype: Gasoline.

As the loop is traversed I see each record in inner loop one by one for each record of outer loop.

In Outer loop when account id 102 and  accoint id id= 102 in inner loop I see enginetype getting populated as Jet as expected.

But during next iteration of outer loop when Accountid = 103 in inner loop Accountid 102 did not retain update from previous iteration of outer loop. For Accountid 102 engine type is blank for this iteration. For Accountid 103 engine type is populated as Gasoline.

I tried to access data from previous iteration of outer loop but was unsuccessful.

How to retain the update from previous iteration of outer loop?

Perhaps iteration is not the correct solution.  Is there a different way of accomplishing this?

Thanks for your help.

prxshah00 - Aug 16, 2020 - 8:31pm
+ Add a Comment
Login