1 Reply Latest reply: Jun 4, 2015 2:45 PM by Marcus Sommer RSS

    Synthetic key issue

    gerry castellino

      Running into a strange issue where a synthetic key is being created on a hierarchy table.

       

      My main hierarchy table is HIERARCHY_ALL.

       

      I'm seeing a synthetic key created on "HIERARHCY_ALL_TEMP-1"  - not sure how this table is being created.

       

      .qvs file below and screenshot attached.

       

      Gerry.

       

      ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

      //-----------------------------------------------------------------------//

      // Description:  Table HIERARCHY

      //-----------------------------------------------------------------------//

      TRACE >> Loading table HIERARCHY_ALL;

       

      [HIERARCHY_ALL_TEMP]:

      Hierarchy(ChildID, ParentID, Location, ParentLocation, Location, Path, \, NodeDepth)

       

      load

       

      HIERARCHY_CATEGORY & '-' & HIERARCHY_NAME & '-' & NODE_NAME & '-' & CONTEXT_ID as ChildID,

      HIERARCHY_CATEGORY & '-' & HIERARCHY_NAME & '-' & PARENT_NODE & '-' & CONTEXT_ID as ParentID,

      NODE_NAME as Location,

      HIERARCHY_CATEGORY,

      HIERARCHY_NAME,

      CONTEXT_ID   as HR_CONTEXT_ID,

      TABLE_NAME  & '-' &  CONTRACT_REFERENCE   & '-' & CONTEXT_ID as HIERARCHY_KEY

      FROM [$(Parameters_Path_Qvd_Data)\HIERARCHY_ALL*.qvd](qvd)

      where match(CONTEXT_ID, $(v_context_keys))>0

      ;

       

      For v_level = 1 to 15

      If FieldNumber('Location$(v_level)','HIERARCHY_ALL_TEMP') = 0 then

       

        concatenate (HIERARCHY_ALL_TEMP)

        Load null() as Location$(v_level)

        autogenerate 0;

       

      ENDIF             

      Next

       

      [HIERARCHY_ALL]:

      NoConcatenate

       

      load * ,

       

       

      if(IsNull(Location15),

      if(IsNull(Location14),

      if(IsNull(Location13),

      if(IsNull(Location12),

      if(IsNull(Location11),

        if(IsNull(Location10),

        if(IsNull(Location9),

        if(IsNull(Location8),

        if(IsNull(Location7),

                    if(IsNull(Location6),

                            if(IsNull(Location5),

                                if(IsNull(Location4),

                                        if(IsNull(Location3),

                                               if(IsNull(Location2),

                                                      (

                                                             Location1

                                                      ),

                                                      (

                                                             Location1 & '|' &

                                                             Location2

                                                      )

                                               ),

                                               (

                                                      Location1 & '|' &

                                                      Location2 & '|' &

                                                      Location3

                                               )

                                        ),

                                        (

                                               Location1 & '|' &

                                               Location2 & '|' &

                                               Location3 & '|' &

                                               Location4

                                        )

                                 ),

                                 (

                                        Location1 & '|' &

                                        Location2 & '|' &

                                        Location3 & '|' &

                                        Location4 & '|' &

                                        Location5

                                 )

                           ),

                           (

                                 Location1 & '|' &

                                 Location2 & '|' &

                                 Location3 & '|' &

                                 Location4 & '|' &

                                 Location5 & '|' &

                                 Location6

                           )

                    ),

             (

                    Location1 & '|' &

                    Location2 & '|' &

                    Location3 & '|' &

                    Location4 & '|' &

                    Location5 & '|' &

                    Location6 & '|' &

                    Location7

             )

      ),

      (

                    Location1 & '|' &

                    Location2 & '|' &

                    Location3 & '|' &

                    Location4 & '|' &

                    Location5 & '|' &

                    Location6 & '|' &

                    Location7 & '|' &

         Location8

             )

      ),

      (

                    Location1 & '|' &

                    Location2 & '|' &

                    Location3 & '|' &

                    Location4 & '|' &

                    Location5 & '|' &

                    Location6 & '|' &

                    Location7 & '|' &

         Location8 & '|' &

         Location9

             )

      ),

      (

                    Location1 & '|' &

                    Location2 & '|' &

                    Location3 & '|' &

                    Location4 & '|' &

                    Location5 & '|' &

                    Location6 & '|' &

                    Location7 & '|' &

         Location8 & '|' &

         Location9 & '|' &

         Location10

             )

      ),

      (

                    Location1 & '|' &

                    Location2 & '|' &

                    Location3 & '|' &

                    Location4 & '|' &

                    Location5 & '|' &

                    Location6 & '|' &

                    Location7 & '|' &

         Location8 & '|' &

         Location9 & '|' &

         Location10 & '|' &

         Location11

             )

      ),

      (

                    Location1 & '|' &

                    Location2 & '|' &

                    Location3 & '|' &

                    Location4 & '|' &

                    Location5 & '|' &

                    Location6 & '|' &

                    Location7 & '|' &

         Location8 & '|' &

         Location9 & '|' &

         Location10 & '|' &

         Location11 & '|' &

         Location12

             )

      ),

      (

                    Location1 & '|' &

                    Location2 & '|' &

                    Location3 & '|' &

                    Location4 & '|' &

                    Location5 & '|' &

                    Location6 & '|' &

                    Location7 & '|' &

         Location8 & '|' &

         Location9 & '|' &

         Location10 & '|' &

         Location11 & '|' &

         Location13

       

       

             )

      ),

      (

                    Location1 & '|' &

                    Location2 & '|' &

                    Location3 & '|' &

                    Location4 & '|' &

                    Location5 & '|' &

                    Location6 & '|' &

                    Location7 & '|' &

         Location8 & '|' &

         Location9 & '|' &

         Location10 & '|' &

         Location11 & '|' &

         Location14

       

       

             )

      ),

      (

                    Location1 & '|' &

                    Location2 & '|' &

                    Location3 & '|' &

                    Location4 & '|' &

                    Location5 & '|' &

                    Location6 & '|' &

                    Location7 & '|' &

         Location8 & '|' &

         Location9 & '|' &

         Location10 & '|' &

         Location11 & '|' &

         Location14 & '|' &

         Location15

       

             ))  as hierarchy_tree resident HIERARCHY_ALL_TEMP;

         

      drop table HIERARCHY_ALL_TEMP;

      Hierarchy_Syn_Key.PNG

      TRACE >> Load table HIERARCHY_ALL done;

        • Re: Synthetic key issue
          Marcus Sommer

          You made for your temp-table a wildcard-load (... From Path\File*...) and if one or more from these files have a slightly different structure they won't be automatically concatenated. They will be loaded with an automatically generated number-suffix. In such cases you need a load per filelist, see within the help by "for each ...". And then I think your long load-script over locations could be solved more elegant, maybe per aggregation-load and concat(Location, '|').

           

          - Marcus