Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Where/how to put LOAD statements for fields from different tables?

I extract data from a large database using SQL. I want to do some manipulation in the script, but my problem is the two fields are in different tables. We have an "Opportunity Start Date" in one table, OM, and an "Action Date" in another table, CA. I have done joins in SQL on the appropriate fields to link the tables, but I have the following problem. This script works fine:
LOAD       IF(OppStartDate>='1/1/2012',OppStartDate) As OSD,
      *
OMTable:
Select
...
OM.OppStartDate
FROM
....
LOAD
IF(ActionDate>='1/1/2012',ActionDate As AD,
*
CATable: Select
..
CA.ActionDate
From
..
Now, I want to create a new field "Delay" equal to "ActionDate - OppStartDate", but no matter where I put this, I generate an error:
NetWorkDays(OppStartDate,ActionDate) As Delay
If I put this statement in the LOAD before the OMTable Select, I get the error "Field 'Action Date' not found", but if I put that statement before the LOAD for CATable, I get "Field 'OppStartDate' not found". (And I tried various combinations of my new names, eg NetWorkDays(OppStartDate,AD), etc. with no better luck.) I had the company SQL expert look at it, and he advised me to join the tables CA and OM, which I did, but I still get this error. I don't think I can use the MAP function because OSD doesn't exist in CATable, and AD doesn't exist in OMTable. Any thoughts?
4 Replies
Not applicable
Author

Table1:-

LOAD PRIMARYKEY,

     *;

SQL SELECT * FROM YADA;

LEFT JOIN (Table1)

LOAD PRIMARYKEY,

     *;

SQL SELECT * FROM BING;

Table2:

LOAD NetWorkDays(OSD-AD),

     *

RESIDENT Table1;

Drop Table1;

Not applicable
Author

Thanks, just a couple of more questions:

What is the syntax for the PRIMARY KEY above? When I do my SQL join, it's on the field OM.RecID=CA.LOPRecID. So for the first table is it just "LOAD RecID, *" or do I need to give it a field name?

Also, normally, I put my table names AND my LOAD statements BEFORE the Select statement. Do I put this third LOAD statement AFTER the entire SELECT statement for CATable (i.e. after the FROM and WHERE clauses in the SELECT)?

Thanks!

Not applicable
Author

Qlikview joins on Like Named fields, the above was just an example since I do not know your dataset

Table 1:

Field A, Field B;

Table 2:

Field XYZ AS Field A (differently named field but is really the same as field A above)

Not applicable
Author

Thanks again, but I really need EXACT syntax, since this is a huge query and I'm getting dirty looks from the system guys for throwing it at the machine repeatedly! ;}
Here's what I have now (which doesn't work - the query just hangs)
LOAD
   OppRecords, // This is my primary key
   * ;
OMTable:
Select
...
OM.RecID as "OppRecords",
...
FROM ...
LOAD
    OpRecord As OppRecords
    NetworkDays(OppStartDate,OnDate) As Delay
    *
    Resident OMTable;
CATable:
Select
CA.LOPRECID As "OpRecord", //This is the index to "RecID" in the OMTable
...
FROM...
Your first example had three LOAD statements; where does the 3rd one go?