Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two main tables:
1 table for Parent Key and basic data associated to that Parent Key
1 table for Child Key and basic data associated to that Child Key
They are linked to each other via a Parent-ChildBridge field
Then there is one table (ImportantData) with data at a Parent Key level. There could be many rows for a single Parent Key. I then loaded a separate table via a Left Join which maps ImportantData to a Child Key. This is causing a loop and some undesirable data.
Dropping the field Parent Key from Table:ImportantData fixes the loop but I do not want to lose the Parent Key field from that table because there are some data that is only associated to a Parent and not to a Child.
Essentially I am trying to load Table:ImportantData to show the below without causing a loop:
Parent Key | Child Key | Key Link | Important Data |
---|---|---|---|
1001 | 123 | 123456789 | 300 |
1001 | 456 | 567890123 | 500 |
2001 | 789 | 456789012 | 800 |
3001 | 555554444 | 2000 |
Any thoughts?
Try
ParentTableMain:
LOAD * INLINE [
Parent Key, Basic Parent Data, Parent-Child Bridge
1001, 10000, ZZZ
2001, 40000, BBB
3001, 50000,
];
Left join(ParentTableMain)
ChildTableMain:
LOAD * INLINE [
Child Key, Basic Child Data, Parent-Child Bridge
123, 555, ZZZ
456, 444, ZZZ
789, 333, BBB
];
FinalParent:
NoConcatenate Load
[Parent Key]&'|'&[Parent Key] As ComboKey,
[Parent Key], [Basic Parent Data], [Parent-Child Bridge],[Child Key], [Basic Child Data]
Resident
ParentTableMain
;
Drop Table ParentTableMain;
ImportantData:
LOAD * INLINE [
Parent Key, Key Link, Important Data
1001, 123456789, 300
1001, 567890123, 500
2001, 456789012, 800
3001, 555554444, 2000
];
Left Join(ImportantData) //This maps a Child Key to a Key Link from ImportantData
LOAD * INLINE [
Key Link, Child Key
123456789, 123
567890123, 456
456789012, 789
];
FinalImportantData:
NoConcatenate Load
[Parent Key]&'|'&[Parent Key] As ComboKey,
[Key Link], [Important Data]
Resident
ImportantData;
Drop Table ImportantData;
If i do a Left Join from the mapping table, I get this kind of detected loop.
Rather than a left join, I simply loaded the mapping table as a static table ("ImportantDataMappingtoChild") but also getting a loop.
Hi Mark,
Its looks like Circular Loop. You should change field value (Primary Key) in any one of the table then you will get expected results.
Thanks,
Lawrance A
Which Primary key in particular? If I change the field name, won't it lose the joins to the other tables?
Try
ParentTableMain:
LOAD * INLINE [
Parent Key, Basic Parent Data, Parent-Child Bridge
1001, 10000, ZZZ
2001, 40000, BBB
3001, 50000,
];
Left join(ParentTableMain)
ChildTableMain:
LOAD * INLINE [
Child Key, Basic Child Data, Parent-Child Bridge
123, 555, ZZZ
456, 444, ZZZ
789, 333, BBB
];
FinalParent:
NoConcatenate Load
[Parent Key]&'|'&[Parent Key] As ComboKey,
[Parent Key], [Basic Parent Data], [Parent-Child Bridge],[Child Key], [Basic Child Data]
Resident
ParentTableMain
;
Drop Table ParentTableMain;
ImportantData:
LOAD * INLINE [
Parent Key, Key Link, Important Data
1001, 123456789, 300
1001, 567890123, 500
2001, 456789012, 800
3001, 555554444, 2000
];
Left Join(ImportantData) //This maps a Child Key to a Key Link from ImportantData
LOAD * INLINE [
Key Link, Child Key
123456789, 123
567890123, 456
456789012, 789
];
FinalImportantData:
NoConcatenate Load
[Parent Key]&'|'&[Parent Key] As ComboKey,
[Key Link], [Important Data]
Resident
ImportantData;
Drop Table ImportantData;
Just cutting of the link between the Parrenttablemain , ChildtableMain will give you the expected result