Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
JustinDallas
Valued Contributor II

Join Tables without Freezing

Hello Everyone,

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

QlikPic.PNG

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.

1 Solution

Accepted Solutions
JustinDallas
Valued Contributor II

Re: Join Tables without Freezing

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.

View solution in original post

6 Replies
Employee
Employee

Re: Join Tables without Freezing

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
Valued Contributor II

Re: Join Tables without Freezing

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

Have you considered using ApplyMap instead of joining?

Don't join - use Applymap instead

Employee
Employee

Re: Join Tables without Freezing

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

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
Valued Contributor II

Re: Join Tables without Freezing

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.

View solution in original post