topic Re: Left join not loading in QlikView

Left join not loading

Wed, 25 Nov 2020 16:16:04 GMT

I have two tables that I am trying to join.

First one has 3M rows and Second 2.8 M.

Here is the script am trying to use:

QUALIFY*;

UNQUALIFY OPRID;

JANJOIN:

LOAD OPRID,

ROLENAME,

CLASSID,

MENUNAME

FROM JanJoin.txt

QUALIFY*;

UNQUALIFY OPRID;

LEFT JOIN (JANJOIN)

LOAD OPRID,

ROLENAME,

CLASSID, y

MENUNAME

FROM MayJoin.txt

It fetches all rows in 20 seconds and than in freezes up every time I try to load.

Is there something wrong with the script or is it a memory limitation issue?

Re: Left join not loading

sunny_talwar — Wed, 18 Jan 2017 14:05:33 GMT

Is this a one to one join or is this one to many or many to many join?

Re: Left join not loading

Wed, 18 Jan 2017 14:10:25 GMT

many to many

Re: Left join not loading

swuehl — Wed, 18 Jan 2017 14:25:58 GMT

So in worst case, this could lead to a resulting table with record number in the order of 1E12?

(Probably not that much in real case, but may explain why your computer freezes)

What are you trying to achieve? Maybe there is a better solution.

Re: Left join not loading

Wed, 18 Jan 2017 14:35:51 GMT

I am trying to find out which new users were added between May and Jan (OPRID is a username).

The thing: there is multiple criteria for those users (e.g. AccountDisabled <>1, etc). This is all stored in different tables.

In order to get that I ran MayJoin and JanJoin queries in Access. These were LEFT JOINS of 4 tables. Hence the number of records.

Re: Left join not loading

swuehl — Wed, 18 Jan 2017 14:42:12 GMT

But basically, shouldn't you end up with two list of users, one for Jan and one for May, with a single record per user? Why are you coping with multiple records per user? And why are you trying to join the tables?

There are multiple ways to identify new customers, script or front end based, and you can find examples in the forum.

For example:

New customer analysis using set analysis

Re: Left join not loading

Wed, 18 Jan 2017 14:48:06 GMT

I forgot to mention I also need to do another Join which will give New Roles for Existing Users.

So let's say Each User has multiple Menus and Each Menu has Multiple Roles.

That is why ended up with multiple user rows.

Re: Left join not loading

swuehl — Wed, 18 Jan 2017 14:54:29 GMT

Ok, probably I over simplified what you are trying to do.

Well, as stated above I think your join will need to much memory / create too many rows.

You can test this by limiting your two tables to a subset of records (e.g. using FIRST ... prefix or a WHERE clause). Maybe like

FIRST 100

LOAD Key, ....

FROM A;

LEFT JOIN

LOAD Key, ....

FROM B

WHERE EXISTS(Key);