Merge Two collections

[Deepshikha21]

Hello,

I have parent child gallery in my app due to all the filters and logic I end up having two different collection . One collection is having Parent gallery data and Other collection is having child gallery data. I need to merge this two collection and need one collection as final. It will be one to many relationship. 

I tried merging using FOR All but I am getting table values from child collection.

Below are the two tables I need to merge one basis of common column i.e. KPI name in my case-

The formula I am using to merge this is -

Clear(collFinalData);

ForAll(collFy,
Collect(collFinalData,
Add Columns(
collKPIDetails,
"ActualValue",
Filter(collFy,KPIName = collKPIDetails[@KPIName],ActualValue).ActualValue,
"ValueYear",
Filter(collFy,KPIName=collKPIDetails[@KPIName],ValueYear).ValueYear,

"ValueQuarter",
Filter(collFy,KPIName=collKPIDetails[@KPIName],ValueYear).ValueQuarter,

"ReportedDate",
Filter(collFy,KPIName=collKPIDetails[@KPIName],ValueYear).ReportedDate,

"FinancialYear",
Filter(collFy,KPIName=collKPIDetails[@KPIName],ValueYear).FinancialYear

)
)
)

[CNT · Accepted solution]

@Deepshikha21 I've made a sample with some simple column names. You can follow the same pattern and use it with your collections and column names.

ClearCollect(col1, {Name:"Tom",ID:1}, {Name:"Mark",ID:2});
ClearCollect(col2, {Name:"Tom", Score:10, Rank:1}, {Name:"Tom", Score:20, Rank:3},{Name:"Mark", Score:15, Rank:2},{Name:"Mark", Score:25, Rank:4});
ClearCollect(col3, AddColumns(col2, "ID", LookUp(col1 As tempCol1, Name=tempCol1.Name).ID))

Please remember to give a 👍 and accept the solution as it will help others in the future.

[Deepshikha21]

Hi @CNT , thanks for your response but as per your example col3 will be having values as below -

and as per my example I need output collection as below -

I need all the values where KPI Name is same in both the collection and that's why I am using filter instead of lookup.

[CNT]

@Deepshikha21 

Please check again. This is what I'm getting,

The code again,

ClearCollect(colComb1, {Name:"Tom",ID:1}, {Name:"Mark",ID:2});
ClearCollect(colComb2, {Name:"Tom", Score:10, Rank:1}, {Name:"Tom", Score:20, Rank:3},{Name:"Mark", Score:15, Rank:2},{Name:"Mark", Score:25, Rank:4});
ClearCollect(colComb3, AddColumns(colComb2, "ID", LookUp(colComb1 As tempColComb1, Name=tempColComb1.Name).ID))

[Deepshikha21]

It works, Thanks A lot @CNT  !!😊

[CNT]

@Deepshikha21 Glad to help!