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

Two Fields in Two tables - How to link?

I'm sure this is a common enough issue, but I cannot get my head around how to fix it. In my opinion it is a problem with the way the original data is set up but I don't think that can be helped at this stage.


I have two Tables:

EnquirySummary Table

EnquiryIDOpenedDate
UPRN
USRN
EnquiryTypeAlt_Addr
1501/08/2010185017614
EY
1601/08/2010
13146EY
1701/08/2010
4036EY
1801/08/2010185136759
EY

AddressInfo Table

UPRN
USRNWARD_NAME
1850176145409GALWALLY
1851367593258LADYBROOK
18735013113146BOTANIC
1873501284036DUNMURRY



So as you can see, both tables have a UPRN or a USRN column

I need to be able to filter the Enquirys table down using the Ward Name

Each row in EnquirySummary will only have either a UPRN or a USRN, not both. The AddressInfo table holds both.

As it is, Qlikview willl create a synthetic table inbetween.

I tried creating a unique Key of combined UPRN and USRN concatenated but it wont work as obviously the records in EnquirySummary will never have both values in their key.

11 Replies
Not applicable
Author

Have you tried a mapping load? This will map the WARD_NAME into the Enquiry table. Alternatively you could just keep the key field to tie the two datasets together.

KeyTable: // generate a table with every possible key

LOAD 'UPRN-'&UPRN as EnquiryKey,

     Ward_Name

FROM AddressInfo;

LOAD 'USRN-'&USRN as EnquiryKey,

     Ward_Name

FROM AddressInfo;

MapEnquiry:

Mapping Load

    EnquiryKey,

    WARD_NAME

FROM KeyTable;

Address:

LOAD

     applymap('MapEnquiry, EnquiryKey) as WARD_NAME,

     *;

LOAD If(USRN = '','UPRN-'&UPRN, 'USRN-'&USRN) as EnquiryKey,

     *

FROM AddressInfo

DROP TABLE KeyTable;

Not applicable
Author

Not sure, but think that might mess up your counts, if you want to calculate the average #enquiry's per ward (cos wards are now duplicated).

I think I would resolve this during load by replacing all the USRN's in enquirysummary with the corresponding UPRN's during LOAD. That way you only link to UPRN's. If you need to know if it originally was a USRN or UPRN, add a boolean is_USRN.

Not applicable
Author

I don't knw whether it will solve ur purpose or not but u can try it:

May be u can concatenate the two fields UPRN and USRN as one field.like:

Load

UPRN as Commonfield

from EnquirySummary.qvd;

concatenate

Load USRN as Commonfield

from EnquirySummary.qvd;

In the Similar manner

Cooncatenate the two fields of the other table.

And then u can link Summarytable and AddressInfotable..

Hope u have got some Idea what I am trying to say...

Not applicable
Author

I think logically this makes the most sense here. But I can't figure out the syntax to do it. Can you nest load statements within an IF statement in the body of another Load statement?

Something like (which doesnt work by the way!):

CTS_ENQUIRY_SUMMARY:

BUFFER (Stale After 24 hours)

LOAD

ENQUIRYTYPE,

DayStart(OPENEDDATE) As OPENEDDATE,

ALT_ADDR,

UPRN,

STREET_NO;

    IF (NOT isnull(STREET_NO),

        LOAD UPRN Resident PointerGrab,

        STREET_NO

    )AS STREET_NO,

ENQUIRY AS ENQUIRY_ID

WHERE

ENQUIRYTYPE='E'

;

SQL SELECT *

FROM CORPDEV."CTS_ENQUIRY_SUMMARY"

ORDER BY OPENEDDATE ASC;

nagaiank
Specialist III
Specialist III

Please see the attached qvw file, which does what you want (loading WARD_NAME field in the Enquiry Summary Table.

The script used is given below:

Address:

LOAD * Inline [

UPRN1,USRN1,WARD_NAME

185017614,5409,GALWALLY

185136759,3258,LADYBROOK

187350131,13146,BOTANIC

187350128,4036,DUNMURRY

];

Enquiry:

LOAD * Inline [

EnquiryID,OpenedDate,UPRN,USRN,EnquiryType,Alt_Addr

15,01/08/2010,185017614,,E,Y

16,01/08/2010,,13146,E,Y

17,01/08/2010,,4036,E,Y

18,01/08/2010,185136759,,E,Y

];

Left Join (Enquiry) LOAD UPRN1 as UPRN,WARD_NAME as N1 Resident Address Where not(IsNull(UPRN1));

Left Join (Enquiry) LOAD USRN1 as USRN,WARD_NAME as N2 Resident Address Where not(IsNull(USRN1));

DROP Table Address;

NULLASVALUE N1, N2;

Left Join (Enquiry) LOAD EnquiryID, (N1 & N2) as WARD_NAME Resident Enquiry;

DROP Fields N1, N2 From Enquiry;

Hope this helps.

Not applicable
Author

Well, are both source tables SQL? Then you can simply load your Enquiry table with

SELECT EnquiryID,
OpenedDate,
COALESCE( EnquirySummary.UPRN, AddressInfo.UPRN) as UPRN,
EnquiryType,
Alt_Addr
FROM EnquiryID
LEFT JOIN AddressInfo A on EnquirySummary.USRN = AddressInfo.USRN

btw, didnt test it, but should be about correct.

gl Jeroen

nagaiank
Specialist III
Specialist III

I have modified my solution slightly. This works.

Address:

LOAD * Inline [

UPRN1,USRN1,WARD_NAME

185017614,5409,GALWALLY

185136759,3258,LADYBROOK

187350131,13146,BOTANIC

187350128,4036,DUNMURRY

];

Enquiry:

LOAD * Inline [

EnquiryID,OpenedDate,UPRN,USRN,EnquiryType,Alt_Addr

15,01/08/2010,185017614,,E,Y

16,01/08/2010,,13146,E,Y

17,01/08/2010,,4036,E,Y

18,01/08/2010,185136759,,E,Y

];

Left Join (Enquiry) LOAD UPRN1 as UPRN,WARD_NAME as N1 Resident Address;

Left Join (Enquiry) LOAD USRN1 as USRN,WARD_NAME as N2 Resident Address;

NULLASVALUE N1, N2;

Left Join (Enquiry) LOAD EnquiryID, (N1 & N2) as WARD_NAME Resident Enquiry;

DROP Fields N1, N2 From Enquiry;

Not applicable
Author

Hi Jeroen - The tables are both loaded from oracle databases, but from two different oracle databases. I assume I can't join in the SQL statement then?

Not applicable
Author

If they are on the same server in different schemas, you can just put schemaname with a period in front of tablename. Like from schema.table left join schema2.table2. Let me know if that's the case.