Hierarchy

[#1]

Hi,

I'm using this script to define the hierarchy in one tabel of my datamodel:

A_HIERARCHY:

Hierarchy (CUSTOMER_A_UNIQUEID,CUSTOMER_F_PARENTAPP,CUSTOMER_A_NAME, [ParentName],[PathSource],[PathName])

LOAD

CUSTOMER_A_BEHAVIOUR,

  CUSTOMER_A_COMMENT,

  CUSTOMER_A_CREATEDTS,

  CUSTOMER_A_DESCRIPTION,

  CUSTOMER_A_LASTMODIFTS,

  CUSTOMER_A_LLPVERSION,

  CUSTOMER_A_NAME,

  CUSTOMER_A_SEARCHNAME,

  CUSTOMER_A_UNIQUEID AS %%CUSTOMER_A_UNIQUEID,

  CUSTOMER_A_UNIQUEID,

  CUSTOMER_A_XTRALINES,

  CUSTOMER_F_CRTIMEZONE,

  CUSTOMER_F_LASTMODSUB,

  CUSTOMER_F_PARENTAPP,

  CUSTOMER_F_PARTITION,

  CUSTOMER_F_PARTITION AS %PARTITIONCUSTOMER_A_UNIQUEID

FROM $(vQVDFolder)\APPLICATION.QVD (qvd);

I expected that PathName would return ParentName/ChildName/Childname. But instead I get '' '/' and '//' I'm sure I'm missing something, how do I define that the pathname should be string/string2/string3 ?

Daniel

[vikasmahajan · Accepted solution]

TRY TO LOAD LIKE FOLLOWING EXAMPLE

HIERARCHY(CHILD_ID, PARENT_ID, [HQ], [Parent Name], [HQ], [Path Name], '/')

LOAD Hqid AS [HQ Code],

     Hqname AS [HQ],

     GeoLevelName AS [Level Name],

     CHILD_ID,

     PARENT_ID,

    Rmcount

FROM

[Source QVDs SIM\DIM_GeoHQMaster.qvd]

(qvd);

vIKAS

[#3]

I don't know why, but it works.

[#4]

One more question,

for

Parent I want to count the records belonging to the parent and all children

for Parent/Child I want to belong the records that belong to the specific combination of parent/child

a simple count will now return records for parent/child while parent is empty. Any ideas on how to achieve this aim?

[vikasmahajan]

You can Count no of items in sql scrpits as I given example I used Rmcount the no of RMCount is the no of regional managers.

Hope this help you.

Vikas