Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Newbie having a loop problem

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.


talk is cheap, supply exceeds demand
5 Replies
MVP & Luminary
MVP & Luminary

Re: Newbie having a loop problem

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


talk is cheap, supply exceeds demand
Not applicable

Re: Newbie having a loop problem

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

MVP & Luminary
MVP & Luminary

Re: Newbie having a loop problem

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Newbie having a loop problem

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

Not applicable

Re: Newbie having a loop problem

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!

Community Browser