Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
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
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
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
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.
Perhaps, RAM is not enough for your join. Try reducing the data size. If that makes it go clear - you get the reason.
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.
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
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
Hi All,
Thanks for all your response. They are all very helpful. Karthikeyan A R, jonathan dienst, Mark Little,
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.