Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Would Qlikview crash while joining 3 different tables from 3 different QVD files in the same load statement

Hi All,

Would Qlikview crash while joining 3 different tables from 3 different QVD files in the same load statement?

I have 3 tables that stored in 3 different large tables from different qvd files into 1 qlikview application.

tbl_A: 30 columns with 300,xxx rows (25MB)

tbl_B: 40 columns with 450,xxx rows (49MB)

tbl_C: 35 columns with 200,xxx rows (21MB)

Following is my code:

Combine:

Load

     Look_Up_Col_1,

    Look_Up_Col_2,

   tbl_A_Col_3, ...

   tbl_A_Col_4, ...

   tbl_A_Col_5, ...

   ...

   tbl_A_Col_30, ...

from (qvd);

join

Load

     Look_Up_Col_1,

    Look_Up_Col_2,

   tbl_B_Col_3, ...

   tbl_B_Col_4, ...

   tbl_B_Col_5, ...

   ...

   tbl_B_Col_40, ...

from (qvd);

join

Load

     Look_Up_Col_1,

    Look_Up_Col_2,

   tbl_C_Col_3, ...

   tbl_C_Col_4, ...

   tbl_C_Col_5, ...

   ...

   tbl_B_Col_35, ...

from (qvd);

Two columns from each of the tables are being used as key for joining them together.

Also I need to use outer join.

I have no idea why my Qlikview always hung after it loaded all rows to the output table (combined).

Do you have any idea? Does it because Qlikview have to use a lot of memory to do the job? I am currently using the QlikView personal edition in a Win 7 computer with 4GB memory. Does it enough for me to run it?

Thanks for your help!

Kind Regards.

1 Solution

Accepted Solutions
buzzy996
Master II
Master II

hi,

seems to be apart from ram,there is another problem in ur model.

could u try to create on central key table with ur look of fields and map all your 3 tables with key table.

that might help.

HTH

View solution in original post

7 Replies
karthikeyan1504
Creator III
Creator III

Hi,

It's crashing because of synthetic keys. I believe you should do the concatenation instead of joining.

In qlikview, if the column names are same in more than one table it will join automatically.

please try as below,

Combine:

Load

     Look_Up_Col_1,

    Look_Up_Col_2,

   tbl_A_Col_3, ...

   tbl_A_Col_4, ...

   tbl_A_Col_5, ...

   ...

   tbl_A_Col_30, ...

from (qvd);

concatenate(Combine)

Load

     Look_Up_Col_1,

    Look_Up_Col_2,

   tbl_B_Col_3, ...

   tbl_B_Col_4, ...

   tbl_B_Col_5, ...

   ...

   tbl_B_Col_40, ...

from (qvd);

concatenate(Combine)

Load

     Look_Up_Col_1,

    Look_Up_Col_2,

   tbl_C_Col_3, ...

   tbl_C_Col_4, ...

   tbl_C_Col_5, ...

   ...

   tbl_B_Col_35, ...

from (qvd);

oknotsen
Master III
Master III

Could you make an estimate about how many rows will be in the final table?

Are all the key columns exactly spelled the same, because you are doing an OUTER join so if that is not the case you will get a Cartesian product which will easily be billions of rows and crash your machine.

May you live in interesting times!
tresesco
MVP
MVP

Perhaps, RAM is not enough for your join. Try reducing the data size. If that makes it go clear - you get the reason.

jonathandienst
Partner - Champion III
Partner - Champion III

You probably do not have enough RAM. If the number of rows in the final result table table is close the the 450k rows of your largest source table, you may be able to get away with it, but if a large number of the composite key values are non-unique or do not match, then the result table can balloon in size.

Try a reduced data load in the debugger to check the operation of your script.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Mark_Little
Luminary
Luminary

Hi,

I would think RAM, would be an issue in this case.

What I would advised it to check wuld the data model looks like when a limit load is run. this would make sure the model is what you want and expect. If not you can look at cleaning it up.

run a debug > Choose limited load and just load ten lines. Check the data model.

Mark

buzzy996
Master II
Master II

hi,

seems to be apart from ram,there is another problem in ur model.

could u try to create on central key table with ur look of fields and map all your 3 tables with key table.

that might help.

HTH

Not applicable
Author

Hi All,

Thanks for all your response. They are all very helpful. Karthikeyan A R, jonathan dienst, Mark Little,

tresesco and Shiva Reddy you are right. I find it is because of the synthetic keys. However, as I really need to use outer join and concatenate is not an option for me, it duplicate number of records. I resolved this problem by combining the two lookup fields into one single field. (i.e. from  Look_Up_Col_1, Look_Up_Col_2, to

Look_Up_Col_1 &'|'& Look_Up_Col_2 as Look_Up_Col) and it works now.

Thanks for all your response that given me idea to fix the problem.