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