1 Reply Latest reply: May 17, 2016 10:59 PM by Settu Periyasamy RSS

    Hierarchy depth in a field

    Christian Wylezol

      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