topic Re: Hierarchy issue - please help! in QlikView

Hierarchy issue - please help!

Thu, 03 May 2012 20:17:22 GMT

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:

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)
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!

Thu, 03 May 2012 20:54:41 GMT

Alexisrokolos,

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

Hierarchy issue - please help!

Fri, 04 May 2012 06:58:30 GMT

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 — Fri, 04 May 2012 19:36:16 GMT

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!

Mon, 07 May 2012 07:24:51 GMT

Hi Stefan

That is exactly the answer I was looking for.

Thank you very much

Alexis