topic Using Table joins to create a formula in Load in QlikView

Using Table joins to create a formula in Load

Thu, 03 Jun 2010 16:20:08 GMT

I'm attempting to pull fields from 3 separate tables in my database, and then perform a calculation on these fields (there are 5) within the Load statement.

For example:

Table 1:

Field A

Field B

Table 2:

Field A

Field C

Field D

Table 3:

Field A

Field E

Right now I'm doing the following (which I know is wrong)

[Join]:

LOAD

Field A,

Field B

SQL SELECT *

FROM Table1;

OUTER JOIN

LOAD

Field A,

Field C,

Field D,

RESIDENT [Table2];

OUTER JOIN

LOAD

Field A,

Field E

RESIDENT [Table 3]

WHERE A < 10;

OUTER JOIN

LOAD

A*B*C*D*E as [Result]

RESIDENT [Join]

GROUP BY "Field A";

Any suggestions would be greatly appreciated! Thank you,

PeterDGA

Using Table joins to create a formula in Load

johnw — Thu, 03 Jun 2010 20:23:06 GMT

Well, your field names need quotes or brakets. Your final outer join has no key field to join on, has the wrong field names, and groups for no apparent reason. Well, I'm assuming that Field A is a unique key. If it isn't, then probably all of your joins are wrong instead of just the last one. But there's no reason you'd be using a unique key in a numeric calculation. I suppose this is just an example, though, so I'll pretend it makes sense that Field A is a unique key AND a value you want to use in a calculation.

LEFT JOIN ([Join])
LOAD
"Field A"
,"Field A"*"Field B"*"Field C"*"Field D"*"Field E" as "Result"
RESIDENT [Join]
;

Using Table joins to create a formula in Load

Thu, 03 Jun 2010 21:13:15 GMT

I included the quotations in my code, and Field A is indeed a unique key and not used in the final calculation.