New table with values calculated from 3 existing tables

[Anonymous]

I would like to create a new table that holds values calculated on the contents of three existing tables.  I cannot alter the way the existing tables are joined in any way or the whole thing falls apart

The table should hold the values:

(Parent-Junctions)	(Child_Junctions)	(Service Groups)
p_Child_Of	c_Child_Of	PSG_Name
p_Parent_To	c_Parent_To

And the calculation would be something like:

If(c_Child_Of = PSG_Name AND p_Parent_To = '', 'Parent_Only',

     If(c_Child_Of = p_Parent_To, 'Parent_And_Child',

          If(c_Child_Of = '' AND p_Parent_To = PSG_Name, 'Child_Only')))     as SG_Identity

Parent_Junctions:

LOAD

PSGJ_Name as p_PSGJ_Name,
PSGJ_Id    as p_PSGJ_Id,
//     PSGJ_ParentGroupFrom,
     PSGJ_ChildGroupTo,
[Child Of] as p_Child_Of,
[Parent To]     as p_Parent_To
FROM

(ooxml, embedded labels, table is Junctions);

Child_Junctions:

LOAD

PSGJ_Name as c_PSGJ_Name,
PSGJ_Id    as c_PSGJ_Id,
PSGJ_ParentGroupFrom,
//     PSGJ_ChildGroupTo,
     [Child Of] as c_Child_Of,
[Parent To]    as c_Parent_To
FROM

(ooxml, embedded labels, table is Junctions);

Service_Groups:

LOAD

PSG_Id    as PSGJ_ParentGroupFrom,
PSG_Id    as PSGJ_ChildGroupTo,
PSG_Id,
PSG_Name,
PSG_Create_From_Child,
PSG_Maximum_Concurrent_IP,
PSG_Minimum_Concurrent_IP,
PSG_Business_Responsible
FROM

(ooxml, embedded labels, table is Groups);

[hector_munoz]

Hi Jonathan,

A trick you could use is to create mapping tables from Child_Junctions and Parent_Junctions tables:

MAP_01:

MAPPING LOAD     PSGJ_ParentGroupFrom     AS [MAP_01 PSGJ_ParentGroupFrom],

                               c_Child_Of                       AS [MAP_01 c_Child_Of]

RESIDENT              Child_Junctions;

MAP_02:

MAPPING LOAD     PSGJ_ChildGroupTo          AS [MAP_02 PSGJ_ParentGroupFrom],

                               p_Parent_To                    AS [MAP_02 c_Child_Of]

RESIDENT              Parent_Junctions;

, 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:

Service_Groups:

LOAD *,

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;
LOAD
PSG_Id    as PSGJ_ParentGroupFrom,
PSG_Id    as PSGJ_ChildGroupTo,
PSG_Id,
PSG_Name,
PSG_Create_From_Child,
PSG_Maximum_Concurrent_IP,
PSG_Minimum_Concurrent_IP,
PSG_Business_Responsible,

ApplyMap('MAP_01', PSGJ_ParentGroupFrom, Null())     AS Service_Groups_c_Child_Of,

ApplyMap('MAP_02', PSGJ_ChildGroupTo, Null())        AS Service_Groups_p_Parent_To

FROM

(ooxml, embedded labels, table is Groups);

Hope it serves!

Regards,H

[Anonymous]

Thank you for your time Hector.  I think this is on the right track and definitely helpful, But I just cannot seem to get it to work and I have no idea why.  It fails out immediately loading "Service_Groups" saying it cannot find the PSGJ_ParentGroupFrom field.

[Anonymous]

I have in the meantime cooked up the correct result in Excel with my original output.  Unless something jumps right out at you, don't spend any more time on this - it was a onetime data validation exercise.  Thanks again.

[hector_munoz]

Hi Jonathan,

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!

Regards,

H