Hierarchy tree not working!

[cristian_av]

Hi

Before all.. I've readed a lot of tutorial about how to create a hierarchy tree, but I just can't make mine to work.

I'm trying to make a hierarchy tree, on wich my fact table has the data show in FACT_TABLE_TEST, and the hierarchy is as shown on the Listado_Terminales table.

I need that when I select an root element of the hierarchy, all the element that belong to that root must also be selected.

Below is the Code I've Created, and hope somebody could help me.

What am I doing wrong??

Thank you!!

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='$ #.##0,00;-$ #.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

Listado_Terminales:

LOAD * INLINE [

AreaNombre, CasinoNombre, TerminalNombre

Industrial,Nivel 16 1/2,CAS_206

Industrial, Nivel 17, CAS_205

Industrial, Nivel 17, CAS_218

Industrial, Nivel 19, CAS_207

Industrial, Pique Directo, CAS_208

Industrial, Pique Directo, CAS_213

Industrial, Sur Sur, CAS_214

Industrial, Sur Sur, CAS_212

Industrial, Hilton, CAS_216

Industrial, Hilton, CAS_211

Industrial, Hilton, CAS_210

Industrial, Hilton, CAS_209

Industrial, Lagunitas, CAS_215

Saladillo, Saladillo, CAS_202

Saladillo, Saladillo, CAS_202M

Saladillo, Saladillo, CAS_203

Saladillo, Saladillo, CAS_203A

Saladillo, Saladillo, CAS_203B

Saladillo, SPPC, CAS_204

];

FACT_TABLE_TEST:

LOAD * INLINE [

FECHA, PERSONA, NODE_ID_Terminal

25-06-2014,PERSONA 16 1/2 Terminal,CAS_206

24-05-2013, PERSONA 16 1/2 Terminal, CAS_206

24-02-2013, PERSONA Saladillo Casino 202, CAS_202

24-01-2013, PERSONA Sal. SPPC, CAS_207

24-08-2013, PERSONAE, CAS_204

24-06-2013, PERSONAE, CAS_213

23-05-2013, PERSONAC, CAS_214

25-05-2013, PERSONAA, CAS_212

21-05-2013, PERSONAZ, CAS_216

];

//Paso 1: Crear la Tabla de Nodos Adyacentes

Terminales_Nodos_Adyacentes:

LOAD Distinct TerminalNombre as VALUE, TerminalNombre & '-Terminal' as NODE_H_ID_Terminal, CasinoNombre & '-Casino' as PARENT_NODE_ID_Terminal resident Listado_Terminales;

LOAD Distinct CasinoNombre as VALUE,CasinoNombre & '-Casino' as NODE_H_ID_Terminal, AreaNombre & '-Area' as PARENT_NODE_ID_Terminal resident Listado_Terminales;

LOAD Distinct AreaNombre as VALUE,AreaNombre & '-Area' as NODE_H_ID_Terminal,  AreaNombre & '-Area' as PARENT_NODE_ID_Terminal resident Listado_Terminales;

DROP TABLE Listado_Terminales;

/*

Hierarchy: Input es Tabla de Nodos Adyacentes. Parámetros:

Node ID : Id del Nodo (Debe Existir)

Parent ID : Id del padre del Nodo (Debe Existir)

NodeName : Nombre del Nodo (Debe Existir)

ParentName : Padre del Nodo (Se crea)

PathSource : String que se usará para crear la ruta hacia el nodo, ya que puede ser distinto al nombre. (Opcional - Debe Existir)

PathName : Ruta hacia el nodo (Se crea)

PathDelimiter : Delimitador ('/', '-' , etc)

Depth : Nivel de profundidad del nodo (Opcional - Se crea)

*/

Terminales_Nodos_Expandidos:

Hierarchy(NODE_H_ID_Terminal,PARENT_NODE_ID_Terminal, ID_Terminal, PARENT_NAME_Terminal, ID_Terminal, Terminales_Arbol, '-') load

  NODE_H_ID_Terminal, //ID del Nodo

  PARENT_NODE_ID_Terminal, //Padre Del Nodo

  VALUE as ID_Terminal //Nombre del Nodo

//VALUE as NAME

RESIDENT Terminales_Nodos_Adyacentes;

Terminales_Belong_To:

HierarchyBelongsTo(NODE_H_ID_Terminal,PARENT_NODE_ID_Terminal,ID_Terminal, 'ANCESTORS_KEY', 'ANCESTORS_NAME', 'DepthDiff')

LOAD

  NODE_H_ID_Terminal, //ID del Nodo

  PARENT_NODE_ID_Terminal, //Padre Del Nodo

  VALUE as ID_Terminal //Nombre del Nodo

RESIDENT Terminales_Nodos_Adyacentes;

DROP TABLE Terminales_Nodos_Adyacentes;

REL_Terminales:

LOAD

  ANCESTORS_NAME as NODE_ID_Terminal,   //NODE_H_ID_Terminal

  NODE_H_ID_Terminal as NODE_H_ID_Terminal

RESIDENT Terminales_Belong_To;

DROP table Terminales_Belong_To;

RENAME FIELD ID_Terminal1 TO Area;

RENAME FIELD ID_Terminal2 TO Casino;

RENAME FIELD ID_Terminal3 TO Terminal;

[cristian_av · Accepted solution]

Finally I solved it!! 😃

If someone needs to do a hierarchy, this is a complete working code, wich selects all the child when you select a parent.

Hope it helps somebody.

Listado_Terminales:

LOAD * INLINE [

AreaNombre, CasinoNombre, TerminalNombre

Area Industrial,Nivel 16 1/2,CAS_206

Area Industrial, Nivel 17, CAS_205

Area Industrial, Nivel 17, CAS_218

Area Industrial, Nivel 19, CAS_207

Area Industrial, Pique Directo, CAS_208

Area Industrial, Pique Directo, CAS_213

Area Industrial, Sur Sur, CAS_214

Area Industrial, Sur Sur, CAS_212

Area Industrial, Hilton, CAS_216

Area Industrial, Hilton, CAS_211

Area Industrial, Hilton, CAS_210

Area Industrial, Hilton, CAS_209

Area Industrial, Lagunitas, CAS_215

Area Saladillo, Saladillo, CAS_202

Area Saladillo, Saladillo, CAS_202M

Area Saladillo, Saladillo, CAS_203

Area Saladillo, Saladillo, CAS_203A

Area Saladillo, Saladillo, CAS_203B

Area Saladillo, SPPC, CAS_204

];

FACT_TABLE_TEST:

LOAD * INLINE [

EJEMPLO_FECHA, EJEMPLO_PERSONA, %Terminal

25-06-2014,PERSONA 16 1/2 Hier_Terminal,CAS_206-Hier_Terminal

24-05-2013, PERSONA 16 1/2 Hier_Terminal, CAS_206-Hier_Terminal

24-02-2013, PERSONA Saladillo Hier_Casino 202, CAS_202

24-01-2013, PERSONA Sal. SPPC, CAS_207

24-08-2013, PERSONAE, CAS_204

24-06-2013, PERSONAE, CAS_213

23-05-2013, PERSONAC, CAS_214

25-05-2013, PERSONAA, CAS_212

21-05-2013, PERSONAZ, CAS_216

];

//Paso 1: Crear la Tabla de Nodos Adyacentes a partir del Listado de Nodos.

Terminales_Nodos_Adyacentes:

LOAD Distinct TerminalNombre as %Hier_KEY_Terminal, CasinoNombre as PARENT_KEY, TerminalNombre as Terminal_Desc  resident Listado_Terminales;

CONCATENATE LOAD Distinct CasinoNombre as %Hier_KEY_Terminal, AreaNombre as PARENT_KEY, CasinoNombre as Terminal_Desc  resident Listado_Terminales;

//En ESTE CASO, el primer Nodo NO tiene padre. Si fuera otro caso, se debe buscar una forma de no agregar un padre a los nodos raiz

CONCATENATE LOAD Distinct AreaNombre as %Hier_KEY_Terminal, /*AreaNombre as PARENT_KEY,*/ AreaNombre as Terminal_Desc   resident Listado_Terminales;

DROP TABLE Listado_Terminales;

Terminal_Hierarchy:

HIERARCHY(%Hier_KEY_Terminal, PARENT_KEY, Terminal_Desc, [Hier_Terminal PARENT NAME], Terminal_Desc, [Arbol Terminales], '|', 'HIERARCHY DEPTH')

LOAD %Hier_KEY_Terminal,

PARENT_KEY,

Terminal_Desc

Resident Terminales_Nodos_Adyacentes;

DROP FIELD [HIERARCHY DEPTH] FROM Terminal_Hierarchy;

DROP FIELD Terminal_Desc FROM Terminal_Hierarchy;

DROP FIELD [Hier_Terminal PARENT NAME] FROM Terminal_Hierarchy;

DROP FIELD PARENT_KEY FROM Terminal_Hierarchy;

Terminal_BelongsTo:

HierarchyBelongsTo(%Hier_KEY_Terminal, PARENT_KEY, Terminal_Desc, 'ANCESTORS_KEY', 'ANCESTORS_NAME', 'Depth')

LOAD %Hier_KEY_Terminal,

PARENT_KEY,

Terminal_Desc

RESIDENT Terminales_Nodos_Adyacentes;

DROP FIELD Depth FROM Terminal_BelongsTo;

DROP TABLE Terminales_Nodos_Adyacentes;

REL_Terminales:

LOAD ANCESTORS_KEY as %Hier_KEY_Terminal,

%Hier_KEY_Terminal as %Terminal

RESIDENT Terminal_BelongsTo;

DROP table Terminal_BelongsTo;

RENAME FIELD Terminal_Desc1 to Hier_Area;

RENAME FIELD Terminal_Desc2 to Hier_Casino;

RENAME FIELD Terminal_Desc3 to Hier_Terminal;