Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 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.
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...
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;
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.
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
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;
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?
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.