Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Two Fields in Two tables - How to link?

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

Two Fields in Two tables - How to link?

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

Two Fields in Two tables - How to link?

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

Re: Two Fields in Two tables - How to link?

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
Valued Contributor III

Re: Two Fields in Two tables - How to link?

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

Re: Two Fields in Two tables - How to link?

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
Valued Contributor III

Re: Two Fields in Two tables - How to link?

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

Re: Two Fields in Two tables - How to link?

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

Re: Two Fields in Two tables - How to link?

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.

Community Browser