Join Tables without Freezing

[JustinDallas]

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.

[Clever_Anjos]

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);

[JustinDallas]

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.

[Colin-Albert]

Have you considered using ApplyMap instead of joining?

Don't join - use Applymap instead

[Clever_Anjos]

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;

[MarcoWedel]

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

[JustinDallas · Accepted solution]

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.