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

Two Fields in Two tables - How to link?

Fri, 10 Jun 2011 15:38:24 GMT

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

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

AddressInfo Table

UPRN	USRN	WARD_NAME
185017614	5409	GALWALLY
185136759	3258	LADYBROOK
187350131	13146	BOTANIC
187350128	4036	DUNMURRY

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.

Two Fields in Two tables - How to link?

Fri, 10 Jun 2011 16:55:19 GMT

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;

Two Fields in Two tables - How to link?

Mon, 13 Jun 2011 08:08:48 GMT

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.

Two Fields in Two tables - How to link?

Mon, 13 Jun 2011 10:41:35 GMT

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...

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

Mon, 20 Jun 2011 15:50:40 GMT

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;

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

nagaiank — Mon, 20 Jun 2011 16:08:26 GMT

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.

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

Mon, 20 Jun 2011 17:53:03 GMT

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

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

nagaiank — Tue, 21 Jun 2011 03:25:09 GMT

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;

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

Tue, 21 Jun 2011 10:24:48 GMT

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?

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

Tue, 21 Jun 2011 10:38:53 GMT

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.

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

Tue, 21 Jun 2011 10:46:48 GMT

Unfortunately they are two different servers.

PointerGrab:
BUFFER (Stale After 24 hours)
CONNECT TO [Provider=MSDAORA.1;User ID=Pointer;Data Source=BCRM] (XPassword is COVNHYdNJTbIXSJOOA);
LOAD 
    UPRN,
    USRN,
    "WARD_NAME";
SQL SELECT *
FROM POINTER."POINTERPLUS_V2"
WHERE TOWN = 'BELFAST';

CONNECT TO [Provider=MSDAORA.1;User ID=corpdev;Data Source=safecity] (XPassword is YCdeBWFNWLZWJ);
CTS_ENQUIRY_SUMMARY:
BUFFER (Stale After 24 hours)
LOAD
ENQUIRYTYPE,
DayStart(OPENEDDATE) As OPENEDDATE,
ALT_ADDR,
UPRN,
STREET_NO As USRN,
ENQUIRY AS ENQUIRY_ID
WHERE
ENQUIRYTYPE='E'
;
SQL SELECT *
FROM CORPDEV."CTS_ENQUIRY_SUMMARY" 
ORDER BY OPENEDDATE ASC;

Two Fields in Two tables - How to link?

Tue, 21 Jun 2011 11:08:52 GMT

Well you may still be able to, but I can't test this for you. You have to put the @servername after the table and use aliases. You might need to put in quote marks somewhere in the names of the schema's, tables and servers, not sure.

Let see, based on your script I would say safecity and BCRM are the database names right? I think you could try:

SELECT Enquiry as Enquiry_ID,
OpenedDate,
COALESCE( E.UPRN, A.UPRN) as UPRN,
EnquiryType,
Alt_Addr
FROM CORPDEV.CTS_ENQUIRY_SUMMARY@safecity E

LEFT JOIN POINTER.POINTERPLUS_V2@BCRM A

on E.USRN = A.USRN

If this works, the other table you can ofc just load plainly.