topic Script is not working with LEFT JOIN in QlikView

Script is not working with LEFT JOIN

Thu, 14 Apr 2011 03:23:01 GMT

Good morning all,

Let me preface this by saying I know that this will be hard to understand as there is so much information, fingers crossed I make some sort of sense though.

Situation summary:

I have an Excel spreadsheet with 2 tabs - this spreadsheet contains Budget information by SalesPerson and Customer.
1. Tab 1 (called SalesBudget$) - this contains the current year budgets
2. Tab 2 (called PreviousYearSalesBudget$) - - this contains the previous year budge
I am using ODBC to connect to a Financial package to extract actual Sales by SalesPerson and Customer.

I have found a way to join all the data together to create 1 table in Qlikview called SALESACTUAL, however my problem is ensuring that table contains all the information I need.

STEP 1 - LOAD the first tab from the Spreadsheet - OK - no issues here

[SalesActual]:

LOAD SalesPerson_Code,

Customer_Code,

Budget_Amount,

FROM

(biff, embedded labels, table is SalesBudget$);

STEP 2 - Load the second tab from the spreadsheet into the new table - OK - no issues here

CONCATENATE (SalesActual)

LOAD SalesPerson_Code,

Customer_Code,

Budget_Amount,

FROM

(biff, embedded labels, table is PreviousYearSalesBudget$);

Step 3 - As I am missing data from the spreadsheet this loads the missing data into the table - OK - no issues here

//This adds the customer OID to the previous table as it is needed to join to customer table later on

LEFT JOIN (SalesActual)

LOAD code as Customer_Code,

oid as myCustomer;

SQL SELECT code,

oid

FROM Customer;

//This adds the Salesperson OID and SalesPerson_Code to the previous table

LEFT JOIN (SalesActual)

LOAD code as SalesPerson_Code,

oid as mySalesPerson;

SQL SELECT code,

oid

FROM SalesPerson;

Step 4 - This loads the ACTUAL data into the table - OK - no issues here

//This adds the ACTUAL values to the previous table as new rows

CONCATENATE (SalesActual)

LOAD date,

myCustomer,

mySalesPerson,

netAmount;

SQL SELECT *

FROM SAAnalysisLine;

This is where I hit my issue. I need to add in the missing data into the table. The rows that I added in Step 3 are missing the SalesPerson_Code and the Customer_Code, thus this section of the script tries to add them in. For some reason this runs, however the missing information is not added into the combined table.

//This adds the Salesperson OID and SalesPerson_Code to the previous table

LEFT JOIN (SalesActual)

LOAD oid as mySalesPerson,

code as SalesPerson_Code;

SQL SELECT oid,

code

FROM SalesPerson;

//This adds the customer OID to the previous table

LEFT JOIN (SalesActual)

LOAD oid as myCustomer,

code as Customer_Code;

SQL SELECT oid,

code

I hope that I have detailed that so that someone can see and understand my issue.

Regards

James

Script is not working with LEFT JOIN

deepakk — Thu, 14 Apr 2011 05:19:16 GMT

hi James,

Assumption : Since you are using Left join its only fetching data with respect to above table hence its not fetching missing data from below.

Use only join and check.

Script is not working with LEFT JOIN

Thu, 14 Apr 2011 05:34:13 GMT

In the third step, you can use join statement alone instead of using Left join. So it will add the missing data.

Third step, you are using left join so you will get the superset of the top table and subset of the bottom table

Script is not working with LEFT JOIN

jonathandienst — Thu, 14 Apr 2011 07:48:29 GMT

James

I see two potential problems with the code:

Looking at the first of your "problem queries" - the fields mySalesPerson and SalesPerson Code already exist in the table. This means the left join will load only those rows matching BOTH of these fields (ie join on both fields). As there is no other field in the query, this does not add any data to your SalesActual table, even if the query returns data. In other words, it is only returning the join fields.
Although this was not part of your question, I see a problem with Step1 and Step2 - you do not differentiate between the values for SalesBudget$ and PreviousYearSalesBudget$. Do you need a Date field?

You may need to construct the table in two parts and then concatenate the two parts for the the final table. Steps 1-3 would be the first table (it does not need to be renamed). For Step 4, construct a separate table (eg tmpActualSales), and run the three queries that build the table. Once done, add something like:

Concatenate (ActualSales)
LOAD * Resident tmpActualSales;

DROP Table tmpActualSales;

Hope that helps
Jonathan

Script is not working with LEFT JOIN

Thu, 14 Apr 2011 08:07:15 GMT

Good afternoon all,

Thank you all for the many and varied responses, it is very much appreciated

I have got it all working now.

Result was that I created 2 MAPPING LOAD tables right at the start and then used APPLYMAP function to add in the fields.

This seems to have fixed my little problem.

Once again thank you to everyone that answered.

Regards

James