5 Replies Latest reply: Sep 14, 2012 11:56 AM by fantasticomk RSS

    Newbie having a loop problem

      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:

       

      not a solution.png

       

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