topic Re: Reducing RAM usage: WHERE EXISTS in QlikView

Reducing RAM usage: WHERE EXISTS

datanibbler — Tue, 16 Apr 2013 10:17:04 GMT

Hi,

I have read about the possibility to reduce the data load in dimension tables when no data exists for these dimensions. The EXISTS function.

I simply enlarge my LOAD statements in the script with

WHERE EXISTS([keyfield_in_other_table], [keyfield_in_this_table])

Correct so far?

The question is: Do I need to specify the real fieldname (when I have used the RENAME functionality to enforce this association)? I just tried it out and it resulted in the dimension table not being loaded at all!?

Can anybody help me with this issue`?

Thanks a lot!

Best regards,

DataNibbler

Re: Reducing RAM usage: WHERE EXISTS

Gysbert_Wassenaar — Tue, 16 Apr 2013 10:41:32 GMT

The fieldname from the other table should be name it has after the table is loaded into qlikviews internal database.

T1:

load A as A1, B as B1, C

from mytable1;

Rename field B1 to B2; // the original B is now B2

T2:

load A2, E, F from mytable2

where exists(A1, A2);

T3:

load B3, G, H from mytable3

where exists(B2,B3);

Re: Reducing RAM usage: WHERE EXISTS

datanibbler — Tue, 16 Apr 2013 14:33:00 GMT

Thanks!

I'll try - I will first see to it that my report displays the correct data and then I can go about trying to optimize it on the resource-side.

Best regards,

DataNibbler

Re: Reducing RAM usage: WHERE EXISTS

datanibbler — Wed, 17 Apr 2013 10:00:49 GMT

Hi Gysbert,

maybe an underlying problem is that I'm a little confused about the terms used in the book "Qlikview 11 for developers": Fact tables and dimension tables - from working with SAP, I'm used to "transaction tables" and "masterdata tables". The MD_tables are such that a key usually consisting of only one field is unique. In the transaction_tables, a unique key must usually consist of several fields.

Am I right in assuming that the "dimension tables" in the book are "masterdata tables" with additional info that is to be added to the records in the "fact tables" and that those basically are what I know as "transaction tables" - such that (the info from) one record from the "dimension tables" is usually attached to multiple records in a "fact table"?

Thanks a lot for clarifying this seemingly simple, but crucial concept!

Best regards,

DataNibbler

Re: Reducing RAM usage: WHERE EXISTS

datanibbler — Wed, 17 Apr 2013 14:27:02 GMT

Hi Gysbert,

something is seemingly not working right:

I have one table here (masterdata, so I guess it's a dimension table) which I want to reduce using that method.

The table is linked to one other via one field which is loaded with the AS option in both tables. Remembering what I read about this function in my book, in this case I can write the function with only one parameter. However, when I enlarge the LOAD statement for that table with

WHERE

EXISTS(%TAG_NUMBER);

I get an error message "Field not found".

What am I doing wrong here?

Thanks a lot!

Best regards,

DataNibbler