Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist 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
Highlighted
Specialist II
Specialist II

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
Highlighted
Employee
Employee

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

Highlighted
Specialist II
Specialist II

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.

Highlighted

Have you considered using ApplyMap instead of joining?

Don't join - use Applymap instead

Highlighted
Employee
Employee

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;


Highlighted

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

Highlighted
Specialist II
Specialist II

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