Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

gerrycastellino
Contributor III

Synthetic key issue

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;

Tags (1)
1 Reply

Re: Synthetic key issue

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

Community Browser