4 Replies Latest reply: May 7, 2012 3:24 AM by alexisrokolos RSS

    Hierarchy issue - please help!

    alexisrokolos

      The attached QVW illustrates a problem that I'm facing and wondered if someone could help.

       

      • I have a number of data sources (I call them S1, S2 and S3)
      • Every person has one unique ID (e.g. 'Alexis''s ID is '57') - this does not vary across data sources
      • A person also has a "family ID"
      • The "family IDs" are not consistent across data sources (e.g. in data source S1 the "family ID" of Alexis (Id=57) is "F101"

             However, the "family ID" of Alexis (ID=57) in data source S2 is "F201"

      • Families comprise of a number of persons but not every data source has the complete list of persons (e.g. if you look at

           data source "S1", family "F101" comprises "Alexis"(ID=57), "Adam"(ID=89) and "Adrian"(ID=92).

      • if you look at data source "S2", family "F201" comprises "Alexis"(ID=57) and Azimut (ID=100)
      • It would appear that the "Alexis" family comprises of "Alexis", "Adam", "Adrian" and "Azimut"

       

       

       

      Requirement:

      1. when I select ANY person to show me just one family code (I have no objection of a new "supercode" to be created that will include all the family members)
      2. When I select that person's family code to display me ALL the family members (Alexis, Adam, Adrian and Azimut in the case of Alexis's family..

       

       

      Any suggestions will be hugely appreciated.

       

       

      Regards

      Alexis

        • Parent/child relationships - please help!
          Joao Paulo Novais

          Alexisrokolos,

           

             Use Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource], [PathName], [PathDelimiter], [Depth])(comandoload | comandoselect)

          • Hierarchy issue - please help!
            Alexis Tan

            Hi,

             

            I dont know if this is what you need^^

             

            since you only gave an inline source, this is all I could do. see below.

             

            Relationships:

            LOAD * INLINE [

            Source, ID, FamilyID

            S1,57,F101

            S1,58,F102

            S1,59,F103

            S1,60,F104

            S1,61,F102

            S1,62,F103

            S1,89,F101

            S1,90,F104

            S1,92,F101

            S1,93,F105

            S1,94,F105

            S1,96,F104

            S2,57,F201

            S2,100,F201

            S2,59,F203

            S2,62,F203

            S2,97,F203

            S2,93,F205

            S2,94,F205

            S2,99,F202

            S2,58,F202

            S3,101,F304

            S3,90,F304

            S3,57,F301

            S3,89,F301

            ]

            ;

             

             

            Join (Relationships) // I joined your 2 tables

            LOAD * INLINE [

            ID, Names

            57,Alexis

            58,Ben

            59,Cathy

            60,Damien

            61,Bertha

            62,Clare

            89,Adam

            90,David

            92,Adrian

            93,Edward

            94,Ellen

            96,Davina

            97,Clarissa

            99,Brian

            100,Azimut

            101,Dora

            ]

            ;

             

            //I created another table "Child" then link them via FamilyID

            QUALIFY*;

            UNQUALIFY FamilyID;

            Child:

            load 

            ID,

            FamilyID,

            Names

            Resident Relationships;

             

            I hope this can help you

             

            Best Regards,

            Alex

              • Re: Hierarchy issue - please help!
                swuehl

                I think you can do it like this (following your existing code):

                 

                SourceList:

                LOAD Distinct Source as SourceList Resident Relationships;

                 

                Let vSourceNum = FieldValueCount('SourceList');

                 

                Master:

                LOAD ID, FamilyID as FamilyID2, autonumber(FamilyID) as MasterID, ID as ID2 resident Relationships where Source = FieldValue('SourceList',1);

                 

                For i = 2 to vSourceNum

                 

                Let vCurrentSource = FieldValue('SourceList',$(i));

                 

                LOAD ID, FamilyID as FamilyID2, autonumber(lookup('FamilyID2','ID2', ID)) as MasterID, ID as ID2 resident Relationships where Source = '$(vCurrentSource)' and exists(ID2,ID);

                 

                LOAD ID, FamilyID as FamilyID2, lookup('MasterID','FamilyID2', FamilyID) as MasterID, ID as ID2 resident Relationships where Source = '$(vCurrentSource)' and not exists(ID2,ID);

                 

                Next

                 

                This creates a table which classifies your ID to MasterID (families). See also attached.

                 

                Hope this helps,

                Stefan

                • Hierarchy issue - please help!
                  alexisrokolos

                  Hi Stefan

                   

                  That is exactly the answer I was looking for.

                   

                  Thank you very much

                  Alexis