topic Re: Newbie having a loop problem in QlikView

Newbie having a loop problem

Fri, 14 Sep 2012 10:49:54 GMT

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

Re: Newbie having a loop problem

Gysbert_Wassenaar — Fri, 14 Sep 2012 11:04:17 GMT

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

Re: Newbie having a loop problem

Fri, 14 Sep 2012 11:25:37 GMT

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

Re: Newbie having a loop problem

Gysbert_Wassenaar — Fri, 14 Sep 2012 11:40:00 GMT

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.

Re: Newbie having a loop problem

Fri, 14 Sep 2012 11:41:13 GMT

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

Re: Newbie having a loop problem

Fri, 14 Sep 2012 15:56:08 GMT

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!