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

How to load related data from another source

Hello all

I would like to know how to load data from a second ODBC source where the first load has
extracted the ID numbers of records that I want in the second load script.

example
TAB_1:


LOAD
"Field_1" AS ID,
"Field_2" AS ID_REF,
"Field_1"&"Field_2" as Unique_ID,
"Field_3" as Task_Date
;

SET NULLDISPLAY='#Null';
SQL SELECT *
FROM ODBC."TABLE_NAME_A"
WHERE ("Field 3" >= '01-DEC-2008')
;


Then subsequently on TAB_2 I want the script to load fields where Unique_ID is equal to
the Unique_ID's loaded in the first part of the script because Field_3 doesn't exist in
the 2nd ODBC source and I don't want to load everything in from that table.
A bit like a join in Access where the record are equal.
BTW, I want the nulls to be like that so I can find data inconsistencies.

TAB_2:

LOAD
"Field_1" AS ID,
"Field_2" AS ID_REF,
"Field_1"&"Field_2" as Unique_ID,
;

SET NULLDISPLAY='#Null';
SQL SELECT *
FROM ODBC."TABLE_NAME_B"
WHERE (TAB_2 "Field_1"&"Field_2" = TAB_1 "Field_1"&"Field_2")
;


Thank you for any guidance and code adjustments you can give.
Many thanks
Greenee

6 Replies
johnw
Champion III
Champion III

I think this, but untested:

TAB_2:
LOAD
"Field_1" AS ID,
"Field_2" AS ID_REF,
"Field_1"&"Field_2" as Unique_ID
WHERE EXISTS(Unique_ID,"Field_1"&"Field_2")
;
SET NULLDISPLAY='#Null';
SQL SELECT *
FROM ODBC."TABLE_NAME_B"
;

Or just join the tables together in SQL, again untested and almost certainly with syntax errors, and doing an inner join which may not be what you wanted:

TAB_2:
LOAD
ID
,ID_REF
,date(date#(Task_Date,'DD-MMM-YYYY')) as Task_Date
,blah_blah_blah
SQL
SELECT A.Field_1 as ID
, A.Field_2 as ID_REF
, A.Field_3 as Task_Date
, B.blah_blah_blah as blah_blah_blah
FROM ODBC.TABLE_NAME_A A
, ODBC.TABLE_NAME_B B
WHERE A.Field_3 >= '01-DEC-2008'
AND B.Field_1 = A.Field_1
AND B.Field_2 = A.Field_2
;

Not applicable
Author

Dohh, that's what I do at the moment in SQL, I didn't realise I could do the same in QV, will test later.
Many thanks John

Not applicable
Author

Hi Again


the first sugestion not at all, the second doesn't seem to work either.
The second load sequence doesn't reference the first set of data that has been collected. When I do a limited load of 10 lines, the second script gets a different 10 lines/ID's that probably do exist in the original tables, but that isn't what I want.

Any other ideas please?

Not applicable
Author

I have attached the file so you can see in detail what I am trying to do.
Many thanks.
Greenee

johnw
Champion III
Champion III

I'm not sure how much more I can help. Your two choices, as I see them, are some sort of where exists(...) or doing the desired join in SQL. If the syntax I gave you is incorrect (likely), or is being translated to the real example incorrectly, or if a limited load causes its own problems, I can't test it, so can't really fix it. I could put together an example with inline loads, but the syntax on those differs some from the syntax on SQL, and I can't mimic the second method at all, so it probably wouldn't be particularly helpful.

Not applicable
Author

Thanks John