Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
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
JustinDallas
Specialist II
Specialist II
Author

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

JustinDallas
Specialist II
Specialist II
Author

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


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
Specialist II
Specialist II
Author

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