11 Replies Latest reply: Jun 21, 2011 7:08 AM by Jeroen Vuurens RSS

    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.

        • Two Fields in Two tables - How to link?
          Leonard Short

          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?

            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.

              • 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;

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

                • Re: Two Fields in Two tables - How to link?
                  Nagaian Krishnamoorthy

                  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?

                      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?

                          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?

                              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?

                                  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?

                                      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.

                            • Re: Two Fields in Two tables - How to link?
                              Nagaian Krishnamoorthy

                              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;