Hierarchy depth in a field

[#1]

Hi,

I hope someone of you can help me and has a good idea.

I have a load script with permanent data from SQL-Database.

I created a "Hierarchy-Load" but my issue is now. that I Need to have the ancestor-level in a separate field for every Reporting-Line.

The created Reporting Lines are not in Order (the red column from the Excel screenshot is missing).

I thought of something like

Let vAncestorDepth = Subfield(vAncestorLevel,',',vAncestorLevel);
But this does not work.

This is my Load-Script

//*****************Element1-Adressen***********************
oas_elmaddrlist_El1:
LOAD
CMPCODE as cmpcode,
ELMCODE as elmcode,
ELMLEVEL,
LSTSEQNO,
NAME AS AdrName,
TAG as AdrTag,
DEFADDR as AdrDefaddr,
ADD1 AS Adr1,
ADD2 AS Adr2,
ADD3 AS Adr3,
ADD4 AS Adr4,
ADD5 AS Adr5,
ADD6 AS Adr6,
POSTCODE as AdrPLZ,
COUNTRY as AdrLand,
TEL AS AdrTel,
FAX AS AdrFax,
LANG AS AdrSprache,
CATAGORY AS AdrCat,
EMAILADDR AS AdrEmail,
TRADERNAMECODE AS AdrTraderCode,
ADDDATE,
LASTUSEDDATE,
TEMPORARYID,
TRADERNAMECODE,
Autonumber(ELMCODE&CATAGORY) as RepTreeKey
;
SQL SELECT *
FROM CODA."OAS_ELMADDRLIST"
WHERE CMPCODE = '$(varCMPCODE_SQL)'
//The other conditions have to be in brackets in order to make the first condition working
AND
(ELMLEVEL = '1'
AND CATAGORY ='HFM' OR CATAGORY ='HGB') //OR CATAGORY ='HYPERION'// we dont Need the old catagories anymore
//AND DEFADDR=1
;
store oas_elmaddrlist_El1 into $(DirDataQVD)oas_elmaddrlist_El1.qvd;
//DROP TABLE oas_elmaddrlist_El1; //Element1-Adressen


//*********************************************************************
//**************** creating Hierarchy from adressfields **********
//*********************************************************************

Let vHierarchyDefinition = 'Adr1,Adr2,Adr3,Adr4,Adr5,Adr6';
Let vNumberOfLevels  = (Len(KeepChar(vHierarchyDefinition,',')) + 1) ;

For  vAncestorLevel = 1 to vNumberOfLevels
Let vAncestor = Subfield(vHierarchyDefinition,',',vAncestorLevel);


Elm1AddressTree:
Load distinct
/*Upper*/([$(vAncestor)]) as ReportingLine, //Upper converts into CAPITAL LETTERS
RepTreeKey
Resident [oas_elmaddrlist_El1];
Next vAncestorLevel 
store Elm1AddressTree into $(DirDataQVD)Elm1AddressTree.qvd;

Does someone of you could help me?

Chris

[settu_periasamy]

Hi Christian,

Do you want the depths field column?  may be try like this..

Elm1AddressTree:
Load distinct
/*Upper*/([$(vAncestor)]) as ReportingLine, //Upper converts into CAPITAL LETTERS
RepTreeKey,

'$(vAncestorLevel)' as Depths

Resident [oas_elmaddrlist_El1];

Next vAncestorLevel