Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have shame for asking, but I've been trying to fix a simple loop with a Key Link table. It seemed so straightforwards in the various articles I read.
Here are my test tables:
Directory;
LOAD Activity_ID,
Cost_Pool_ID,
Period_ID,
Activity_Item
FROM
[Test Activity.xlsx]
(ooxml, embedded labels, table is Activity);
LOAD Cost_Pool_ID,
[Cost_Pool Description],
Ledger_ID
FROM
[Test Cost Pool.xlsx]
(ooxml, embedded labels, table is [Cost Pool]);
LOAD Ledger_ID,
Period_ID,
[Ledger Description]
FROM
[Test Ledger.xlsx]
(ooxml, embedded labels, table is Ledger);
LOAD Period_ID,
[Period Description]
FROM
[Test Period.xlsx]
(ooxml, embedded labels, table is Period);
I carefully followed instructions about creating a link whereby I firstly broke all links by renaming foreign keys and secondly generated the new KeyLink table:
Directory;
LOAD Activity_ID,
Cost_Pool_ID as A_Cost_Pool_ID,
Period_ID as A_Period_ID,
Activity_Item
FROM
[Test Activity.xlsx]
(ooxml, embedded labels, table is Activity);
LOAD Cost_Pool_ID,
[Cost_Pool Description],
Ledger_ID as C_Ledger_ID
FROM
[Test Cost Pool.xlsx]
(ooxml, embedded labels, table is [Cost Pool]);
LOAD Ledger_ID,
Period_ID as L_Period_ID,
[Ledger Description]
FROM
[Test Ledger.xlsx]
(ooxml, embedded labels, table is Ledger);
LOAD Period_ID,
[Period Description]
FROM
[Test Period.xlsx]
(ooxml, embedded labels, table is Period);
KeyLink:
load Distinct
Activity_ID,
A_Cost_Pool_ID as Cost_Pool_ID,
A_Period_ID as Period_ID,
Activity_Item
Resident Activity;
Concatenate
load Distinct
Cost_Pool_ID,
[Cost_Pool Description],
C_Ledger_ID as Ledger_ID
resident [Cost Pool];
Concatenate
load Distinct
Ledger_ID,
L_Period_ID as Period_ID,
[Ledger Description]
resident Ledger;
Concatenate
load Distinct
Period_ID,
[Period Description]
resident Period;
This however resulted in this:
If anyone is able to provide an explanation I would be very grateful. I can't seem to get my head around it at all. I'm sure that once it has clicked into place I'll be off!
Additional bonus question: Why use 'Load Distinct' instead of just 'Load'?
Thank you!!
I don't think you are supposed to include anything but the keys in the keylink. So the descriptions should be left out. That prevents the creation of the synthetic keys you now have.
I think that you can get rid of your loop by just renaming Period_ID to L_Period_ID where you load from [Test Ledger.xlsx]
LOAD Ledger_ID,
Period_ID as L_Period_ID,
[Ledger Description]
FROM
[Test Ledger.xlsx]
(ooxml, embedded labels, table is Ledger);
No need for the keylink convolution
Hmmm, I'll give it a go. For educational purposes - what was I doing wrong with the keylink convolution? Thanks.
I don't think you are supposed to include anything but the keys in the keylink. So the descriptions should be left out. That prevents the creation of the synthetic keys you now have.
Aha! Thank you that makes sense.
I tried leaving out the loop but that just results in no cost value pulled in when looking at activity by period.
I'll keep trying.
Thank you for your answers so far, I'm very grateful for your assistance.
Cheers x
Yes it was my confusion re: adding everything in the key link table that was the problem. Using only the Keys (seems obvious now) I've removed the loop and can now view activity and costs by period. Lovely. Thank you very much for speedy and helpful responses - cheers!