A trick you could use is to create mapping tables from Child_Junctions and Parent_Junctions tables:
MAPPING LOAD PSGJ_ParentGroupFrom AS [MAP_01 PSGJ_ParentGroupFrom],
c_Child_Of AS [MAP_01 c_Child_Of]
MAPPING LOAD PSGJ_ChildGroupTo AS [MAP_02 PSGJ_ParentGroupFrom],
p_Parent_To AS [MAP_02 c_Child_Of]
, and loading necessary fields from them into table Service_Groups with the idea if creating a nested LOAD in which you cretaed the necessary calculated field:
If(Service_Groups_c_Child_Of = PSG_Name AND Service_Groups_p_Parent_To = '', 'Parent_Only',
If(Service_Groups_c_Child_Of = Service_Groups_p_Parent_To , 'Parent_And_Child',
If(Service_Groups_c_Child_Of = '' AND Service_Groups_p_Parent_To = PSG_Name, 'Child_Only'))) as SG_Identity;
PSG_Id as PSGJ_ParentGroupFrom,
PSG_Id as PSGJ_ChildGroupTo,
ApplyMap('MAP_01', PSGJ_ParentGroupFrom, Null()) AS Service_Groups_c_Child_Of,
ApplyMap('MAP_02', PSGJ_ChildGroupTo, Null()) AS Service_Groups_p_Parent_To
[C:\Users\uslovjon\Desktop\Service_Groups\20170413_Groups and Junctions(VLUP).xlsx]
(ooxml, embedded labels, table is Groups);
Hope it serves!
I think I know where the error is... Try to replace previous ApplyMap() sentences by:
ApplyMap('MAP_01', PSG_Id, Null()) AS Service_Groups_c_Child_Of,
ApplyMap('MAP_02', PSG_Id, Null()) AS Service_Groups_p_Parent_To
I did not realize that PSGJ_ParentGroupFrom and PSGJ_ChildGroupTo were alias instead of original field names...
Hope that this really works fine!