topic Re: Join Tables without Freezing in QlikView

Join Tables without Freezing

JustinDallas — Mon, 26 Jan 2015 21:39:15 GMT

Hello Everyone,

So I have a data structure that currently looks like this.

What I want to do is join the Fact and SalesPlanKeys table with each other, so that the the Fact table would look like this

Fact -

%ShipTo

%Vendor

%SalesPlanKey

and my SalesPlanKeys table gets dropped. I've tried the following statement, but Qlikview always freezes on me.

My latest harebrained attempt to join the two tables into a third one ends up with "NewFactTable doesn't exist"

Fact:

LOAD %Vendor, %ShipTo FROM

(qvd);

SalesPlanKeys:

LOAD %ShipTo, %SalesPlanKey

FROM

(qvd);

SalesPlan:

LOAD SalesPlan.Year, %SalesPlanKey

FROM

(qvd);

NewFactTable:

LOAD *

Resident SalesPlanKeys;

LEFT JOIN(NewFactTable)

LOAD *

Resident Fact;

DROP Table Fact;

DROP Table SalesPlanKeys;

I know what I want to do, I just can't figure out how to do it with joins. Push come to shove, I'll just daisy chain together some 'applymaps' or leave the linking table as is.

Re: Join Tables without Freezing

Clever_Anjos — Mon, 26 Jan 2015 21:41:53 GMT

If you want to join do it directly

Fact:

LOAD %Vendor, %ShipTo FROM

(qvd);

SalesPlanKeys:

LEFT JOIN LOAD %ShipTo, %SalesPlanKey

FROM

(qvd);

SalesPlan:

LEFT JOIN LOAD SalesPlan.Year, %SalesPlanKey

FROM

(qvd);

Re: Join Tables without Freezing

JustinDallas — Mon, 26 Jan 2015 21:50:33 GMT

Thanks for the suggestion Clever, the reason I want to do it the other way with resident tables is because I think I am going to see that more often out in the wild.

Re: Join Tables without Freezing

Colin-Albert — Mon, 26 Jan 2015 22:38:28 GMT

Have you considered using ApplyMap instead of joining?

Don't join - use Applymap instead

Re: Join Tables without Freezing

Clever_Anjos — Mon, 26 Jan 2015 22:43:24 GMT

It´s freezing because

NewFactTable:

LOAD *

Resident SalesPlanKeys;

LEFT JOIN(NewFactTable)

LOAD *

Resident Fact;

DROP Table Fact;

DROP Table SalesPlanKeys;

does not have fields in common, so your´re generating a cartesian product

Fact:
LOAD %Vendor, %ShipTo FROM

(qvd);
   SalesPlanKeys:
LOAD %ShipTo, %SalesPlanKey
FROM

(qvd);

 SalesPlan:
LOAD SalesPlan.Year, %SalesPlanKey
FROM

(qvd);

 NewFactTable:
LOAD SalesPlan.Year, %SalesPlanKey
resident SalesPlan;

LEFT JOIN LOAD *
Resident SalesPlanKeys;
LEFT JOIN(NewFactTable)
LOAD *
Resident Fact;
DROP Table Fact;
DROP Table SalesPlanKeys;
drop table SalesPlan;

Re: Join Tables without Freezing

MarcoWedel — Tue, 27 Jan 2015 00:43:34 GMT

NewFactTable:

LOAD *

Resident SalesPlanKeys;

does not create a new table. Having all fields in common with SalesPlanKeys it is autoconcatenated with this table instead.

Try

NoConcatenate

NewFactTable:

LOAD *

Resident SalesPlanKeys;

instead.

hope this helps

regards

Marco

Re: Join Tables without Freezing

JustinDallas — Tue, 27 Jan 2015 20:35:33 GMT

Turns out that I'm an idiot. In the data model, %ShipTo isn't a unique field, so what was happening was some kind of backdoor "almost cartesian" product. That's why my memory went so high, and my script took so long to execute. Sorry to waste yalls time when you could have been helping someone that really needed it.