topic Re: Join Issues in QlikView

Join Issues

Wed, 25 May 2011 13:39:56 GMT

I am trying to join two tables: Order__c and Products_OTR__c and using the below fields. When I execute the script it is telling me that Qlikview cant locate the source tables. So basically there is an issue with the expressions.

Any help is greatly appreciated.

Order_Source_Data:

Load

Order__c,

Booked_on_AS400__c,

Rev_Rec__c,

Ship_Date__c,

Total_Order_Amount2__c

From Order__c;

Products_OTR_Source_Data:

Join(Order_Source_Data)

Load

Name,

Product_Family__c,

Quantity__c,

Total_Price__c

From Products_OTR__c;

Join Issues

Wed, 25 May 2011 13:52:22 GMT

Hi,

If what you want is to join the data from Order_c and Products_OTR_c, the script is not correct. It should be similar to this:

Order_Source_Data:

Load

Order__c,

Booked_on_AS400__c,

Rev_Rec__c,

Ship_Date__c,

Total_Order_Amount2__c,

CommonField

From Order__c;

Join

Load

Name,

Product_Family__c,

Quantity__c,

Total_Price__c,

CommonField

From Products_OTR__c;

AND VERY IMPORTANT! you need to have one field in common in both tables Product_OTR_c and Order_c to relate them

Join Issues

nagaiank — Wed, 25 May 2011 14:04:37 GMT

If the tables 'Order__c' and 'Productsf_OTR__c' are from an external source, you should have connected to that source and the tables should exist there.

If these tables are already loaded in the script prior to the join, use 'Resident' instead of 'From'.

Re: Join Issues

Wed, 25 May 2011 14:13:55 GMT

Hi,

Basic thing for Joining is that you should have common fields in both the tables. I see that you dont have them. Hence you cannot join!

I guess that since you are loading OrderHeaderData..normally it does not have a productID.
It is always present in the OrderDetailsData.

what you can do is...

OrderHeader:
Load OrderID,
.......,
.......
From OrderHeaderTable
;
OrderHeaderDetails:
Leftjoin(OrderHeader)
Load OrderID,
ProductID
.....,
....
From OrderHeaderDetailsTable ;

Now you you can attach the ProductTable with the ProductID to this OrderHeaderTable

Re: Join Issues

Wed, 25 May 2011 14:18:36 GMT

what i understood from you post is, Qlikview is not able to find the source table i.e. 'Order__c' and 'Productsf_OTR__c' tables on the specified path.

what is the source of you table? are you loading from DB> which DB and what type of connection do you use (OLEDB or ODBC)?

BTW, for other ppls. You could do joins with out key between the tables also. Most of the Rolling periods logics has been computed with the above concept (without key b/w the tables) but it is not adviceable to do so.

take for example, if we have tabel like this

LOAD * inline [

Test

];

left Join

LOAD * INLINE [

sales

];

on above table there is no key between the table but it would join both the tables and give you cartesian product result as below.

Test,Sales

a ,1

a ,2

a ,3

b ,1

b ,2

b ,3

c ,1

c ,2

c ,3

So conclusion is, you could join both the tables without key, but not advisable and depens on your requirement also.

Hope this helps.

- Sridhar

Re: Join Issues

Wed, 25 May 2011 14:23:21 GMT

It's trying to load Salesforce.com tables