Hierarchy depth in a field

Tue, 17 May 2016 06:05:17 GMT

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

Re: Hierarchy depth in a field

settu_periasamy — Wed, 18 May 2016 02:59:24 GMT

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

topic Hierarchy depth in a field in QlikView

Hierarchy depth in a field

Re: Hierarchy depth in a field