Synthetic Link at report level

[#1]

Hi

I have 2 large data sets which I can't join at the script level as it runs out of virtual memory.

Thus, my idea was to create what I am calling a "Synthetic" link at the report level, which basically is just a trigger that when the field Trade ID is selected, it will select in field to the Trade ID column on the other table.

However, when I use the option Select In Field, only when I have a unique value selected will it select on the second table, which presents a problem as I need it when I am selecting a list of Trade IDs.

I assume this is possible by using a macro with SelectPossibleValues, but I have no technical background I am finding it hard to write the macro that would allow me this.

Thank you in advance to anyone who can help.

[vgutkovsky · Accepted solution]

Lia,

Instead of doing it with a macro, have you considered leaving the 2 table separate (islands in the data model) and using set analysis? For example, if your first table's relevant field is called Field1 and your second table's is called Field2, you can restrict selections with the following simple piece of set analysis in your expressions (using sum of sales as an example): sum({<Field1=P(Field2)>} sales)

Regards,

[Oleg_Troyansky]

Generally speaking, if you have 2 large data sets and you run out of memory in the script (???), trying to resolve the problem in the run-time should cause even a bigger performance issue.

Usually (with very few exceptions) QlikView is much more effective using its native associative logic (which means - linking the tables properly in the script), than with any "synthetic" solutions, including macros, island tables and so forth...

I'd recommend looking for a way to link the data properly in the first place. Try to identify what fields are using most of the memory (typically, the fields with most distinct values) and see if you can eliminate any of them. Worst case - try moving into a larger 64-bit environment.

Also, review your script for possible misspelled field names. If you are trying to join 2 tables, and one of the keys is misspelled, you might be causing a "Cartesian multiplication" which is killing memory for no good reason...

[#4]

My problem is the business requirement of keeping all values from both tables which means a full outer join.

I tried:

full

outer join dbo.OMDConfirmsTradeRCube on (convert(varchar(10),dbo.OMDConfirmsTradeRCube.Trade_ID)||convert(varchar(5),dbo.OMDConfirmsTradeRCube.Trade_Event_Source_Trade_Version))= (convert(varchar(10),dbo.Traction_LadderedRCube.Murex_ID)||convert(varchar(5),dbo.Traction_LadderedRCube.Version))

But it is only giving me the common values between the 2 tables.