topic How to load related data from another source in QlikView

How to load related data from another source

Fri, 14 Aug 2009 02:33:01 GMT

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

How to load related data from another source

johnw — Fri, 14 Aug 2009 03:34:10 GMT

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
;

How to load related data from another source

Fri, 14 Aug 2009 12:08:21 GMT

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

How to load related data from another source

Fri, 14 Aug 2009 21:12:11 GMT

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?

How to load related data from another source

Fri, 14 Aug 2009 21:25:04 GMT

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

How to load related data from another source

johnw — Sat, 15 Aug 2009 02:01:04 GMT

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.

How to load related data from another source

Sat, 15 Aug 2009 03:18:49 GMT

Thanks John