
Joins and data model
Neil Southern Feb 17, 2010 9:44 AM (in response to satanas313)Hi,
A quick and dirty way of doing this without getting too complicated, and if the data is not too large would be to create 2 new tables from whats already in memory doing resident loads and using the qualify and unqualify commands.
Newtable1:
Qualify *;
Unqualify NewKeyField;
Load
*,
oldkeyfield as NewKeyField;
resident oldtable1;
unqualify *;
and replicate for the second table.
You should end up with two data islands. Your original and new qualified island with a single unqualified join on the NewKeyField.
There are many ways of doing this....you may also want to look at Set Analysis,
Regards,
Neil

Joins and data model
satanas313 Feb 17, 2010 10:04 AM (in response to Neil Southern)Thanks a lot for your response.
If I build two "islands" of data how does it work in memory? I will have two sets of data really separated or does the system generates a unique data set with all possible combinations?
For the "Set Analysis" solution, I looked but I own I don't know how to apply this to my problem. I understand the concept but I'm incompetent to build it.
Thanks

Joins and data model
Neil Southern Feb 17, 2010 10:12 AM (in response to satanas313)As I said it would be quick and dirty,
When doing the resident loads, I showed in the stript load *, Keyfield. You could specify only the fields you need to cut down on memory.
The original suggested solution would take double the memory because you are loading the data in twice. It just depends on how much data you are loading. At moment how much memory does the qvw occupy when loaded into developer you can check it in taskmanager.
Regards,
Neil


Joins and data model
Neil Southern Feb 17, 2010 11:26 AM (in response to satanas313)what formula or sums are you trying to do between these 2 tables and i might be able to make some suggestions of how to do it.
Regards,
Neil

Joins and data model
satanas313 Feb 17, 2010 11:44 AM (in response to Neil Southern)The formula is really simply. It's just a division between 2 sum. To my mind It's "only" a data problem and a join problem in particular.
The others joins limits the records of the 2 tables and this causes wrongs sums (wrong because of number of rows). For example if only the 2 tables were joined, we would have 1 000 rows but because of others joins we have 500 rows. So the sums aren't correct...
Thank you for your help

Joins and data model
Neil Southern Feb 17, 2010 12:11 PM (in response to satanas313)You may have to use Set analysis a very simple example below
Here is an example for doing total gross profit
SUM( {1} Margin) / SUM( {1}Sales)
{1} is the set operator and denotes a full set of data.
This will sum all the margin regardless of selection and divide by the sum of all sales regardless of selection.
You may also need to incorporate If Statements eg
SUM( {1} IF(Category = 'Shoes', Margin) ) / SUM( {1} IF(Category = 'Shoes', Sales) )
To give total Gross profit for shoes,
Regards,
Neil

Joins and data model
satanas313 Feb 17, 2010 12:51 PM (in response to Neil Southern)Ok thank a lot for your response I understand the principle.
To take your example, in my case, Margin comes from Table1 and Sales from Table2.
Does set analysis applies to my problem? If I use "{1}" do I have all Margin and all Sales related or do I have all Margin and all Sales like a full outer join?
Tell me if my question needs more explanations.
Thanks






