Newbie having a loop problem

[#1]

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

[Gysbert_Wassenaar]

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

[#3]

Hmmm, I'll give it a go. For educational purposes - what was I doing wrong with the keylink convolution? Thanks.

[Gysbert_Wassenaar · Accepted solution]

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.

[#5]

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

[#6]

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!