How to load only needed data and avoiding outer join

[#1]

Hi everybody,

I have 2 tables as in the examples below:

Table1

Item	Location	Qty
A1	1-1	12
B1	2	0
C1	1-1	10
A1	2	0

Table 2

Location	Capacity
1-1	20
2	30

I want to load the first table with a where clause Qty<>0 since the items with Qty=0 will increase continuously.

Table 1 revised

Item	Location	Qty
A1	1-1	12
C1	1-1	10

My problem is that if I have an location (ex=2)  that is not part of the Table 1 revised I’m losing my link with the Table 2 and implicitly I can’t calculate my total capacity since some locations are missing. The goal is to have the overview of the inventory capacity and to compare it with the actual quantity.  

I have tried to load first the Table 2 and then do an exists on the load of the Table 1 but is not working. Also I tried an outer join but seem that at the very big amount of data is not a good idea.

If anyone had the same issue can you tell me how you solve it ?

Thank you.        

[hic]

I do not understand what the problem is. If you load Table1 with a Where-clause demanding Qty>0 and Table2 as it is, then you get what you want. You can calculate the total capacity still. Or, what am I missing?

HIC

[maksim_senin]

Hi Mariana,

The answers can be more useful when you post script or .qvw file, sometimes they more descriptive than words.

I believe it's just enough to remove "exists" from your script and keep "Qty>0" condition.

Best regards,

Maxim

[#4]

Hi,

I attached to the original post an example.

My problem is that in Table1 I have 420 000 rows and 410 000 have a zero quantity, that means I'm interested in only 10 000 rows from the Table1 + 5 000 rows that have a zero quantity that I need so I can do my link with Table2.

So I'm trying in some way to get from the 410 000 rows only the ones that are part of the Table2.

Thank you,

[hic]

You do not need to load the 5000 records from Table1 that have a zero quantity - the tables will link anyway. And you will get a capacity for Locations that do not exist in Table1.

But if you still want to do it this way, you could use

arsh:

LOAD Warehouse, Location, Capacity

          FROM C:\Users\hurezm\Desktop\Table2.xls (biff, embedded labels);

Trans:

LOAD Item, Warehouse, Location, Qty

          FROM C:\Users\hurezm\Desktop\Table1.xls (biff, embedded labels)

          where Qty>0 or Exists(Location);

But here you have two keys between the tables: Warehous & Location. Do you really want to use both as keys?

HIC

[#6]

Hi,

Yes I need to keep the keys, in fact I have more than this and is possible that this is my problem. But I need to keep all this keys because I don't want to break the data model.

I couldn't get it done only with the qty>0 and exists(Location).  

Thank you