Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The attached QVW illustrates a problem that I'm facing and wondered if someone could help.
However, the "family ID" of Alexis (ID=57) in data source S2 is "F201"
data source "S1", family "F101" comprises "Alexis"(ID=57), "Adam"(ID=89) and "Adrian"(ID=92).
Requirement:
Any suggestions will be hugely appreciated.
Regards
Alexis
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
Alexisrokolos,
Use Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource], [PathName], [PathDelimiter], [Depth])(comandoload | comandoselect)
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
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
Hi Stefan
That is exactly the answer I was looking for.
Thank you very much
Alexis