Assign a Material to a Family with priority

[jorditorras]

Dear Gurus,

I have to assign Materials to a Family Code that is in a external table (I'm using APPLY_MAP for that). The problem is that there are different kind of Family Codes (Group M, Group K and some others...). 

I have to take priority to the group M. That means that, I only assign a Material to his corresponding Family of group K in case that it doesn't exist in the group M. 

After my mappings, I tried to use the following sentence:

WHERE if (FAM_GROUP='M', FAM_GROUP='M', FAM_GROUP='K') 

But it doesn't work as, if one material is contained in both groups (M and K), my script assigns this Material to both families and I want it to be assigned to M.

Any idea about how to solve it?

Thanks so much in advance!!! 🙂

[Or]

If the list is finite and reasonable in length, you could set up a bunch of mapping load, one per family, and then read those in order of priority (easiest using coalesce() probably):

FamilyM:

Mapping Load Field1 as Field1, Field2 as Field2_M

From Table
Where Family_Code = 'M';

FamilyM:

Mapping Load Field1 as Field1, Field2 as Field2_K

From Table
Where Family_Code = 'K';

Final:

Load Field1, Coalesce(

ApplyMap('FamilyM',Field1,null()),

ApplyMap('FamilyK',Field1,null())

) as MappedValue

From YourTable;

Otherwise, your approach could possibly be to create the table with multiple lines and then make a second pass to eliminate any unnecessary lines, grouping by every field except the family group, sorting by family group priority, and keeping only the first line in each group. This could also be achieved with FirstSortedValue() in a similar manner, I think.