topic Re: How to load only needed data and avoiding outer join in QlikView

How to load only needed data and avoiding outer join

Wed, 20 Mar 2013 19:04:06 GMT

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.

Re: How to load only needed data and avoiding outer join

hic — Wed, 20 Mar 2013 20:19:25 GMT

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

Re: How to load only needed data and avoiding outer join

maksim_senin — Wed, 20 Mar 2013 21:28:20 GMT

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

Re: How to load only needed data and avoiding outer join

Thu, 21 Mar 2013 11:24:55 GMT

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,

Re: How to load only needed data and avoiding outer join

hic — Thu, 21 Mar 2013 11:52:51 GMT

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

Re: How to load only needed data and avoiding outer join

Thu, 21 Mar 2013 14:00:51 GMT

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