7 Replies Latest reply: Sep 16, 2011 2:34 AM by Goran Korsgren RSS

    Scripting help

    Muralidhar Koti

      Hi All,

       

      I have got a two table Transaction and NameAddress. Three columns in Transaction table link to same column in NameAddress table. The sample table structures are given below.

       

      Transaction:

      ------------------

      TransactionID

      ClientID

      UnderwriterID

      ThirdPartyID

      Amount

       

      NameAddress:

      ---------------------

      NameID

      FullName

      Address

      other columns

       

      I need to link ClientID with NameID to fetch client name & address details. The same is the case with Underwriter and Thirdparty.

       

      Currently in QlikView I am creating 3 copies of the NameAddress table and joining separately. Is this is best way or there are any other ways to join this?

       

      If having 3 tables is the best way, then the issue I am having is

       

      1. User wants a list box with list of all names. And when he selects a name, data should be displayed if any of Client, Underwriter or Thirparty name matched with selected name from the list box?

       

      Could you plese advice me on how to proceed with this?

       

      Regards,

      Murali

        • Re: Scripting help
          Goran Korsgren

          Hi Murali

           

          I think that's a good approach you started with (3 copies of the NameAddress table).

          (Have a look in the first "More like this" thread here to the right.)

           

          For the search functionality you want, maybe you can do it with a search object, I am not sure.

           

           

          Otherwise you may create a new table (TransNames) which links to Transaction on TransactionID:

           

          Assuming you first have left joined so that you have the three different types of fullnames in the Transaction table

           

          TransNames:

          Load TransactionID,

               ClientFullName As AnyFullName

          Resident Transaction;

           

          Concatenate (TransNames)

          Load TransactionID,

               UnderwriterFullName As AnyFullName

          Resident Transaction;

           

          Concatenate (TransNames)

          Load TransactionID,

               ThirdPartyFullName As AnyFullName

          Resident Transaction;

           

          Now your users can search for '*Koti*' in the AnyFullName field to get all transactions where a person with Koti in the name has been involved in any way (as Client, Underwriter and/or ThirdParty)

           

          hth

          /gg

            • Re: Scripting help
              Muralidhar Koti

              Thanks a lot for your post.

               

              This is exactly how I am currently hadling it in my script. Only change is instead of TransactionID, I am using RowNo() in my script.

               

              But the key issue I am facing here is, because the NameAddress table has close to 2M records, having 3 copies of it and again concatenating to create a new table is taking quite a lot of time to load data and also more RAM space when users access this using access point.

               

              I posted this here to find out what I am doing is correct or there any other better ways to handle this.

                • Re: Scripting help
                  Goran Korsgren

                  Hi again

                   

                  You may consider changing your datamodel so you have

                   

                  Transaction

                  Transaction_ID

                  Amount

                   

                  Agent

                  Transaction_ID

                  Type (here you will have Client/Undertaker/Thirdparty)

                  NameID

                   

                  NameAddress

                  NameID

                  FullName

                  Address

                  other columns

                   

                   

                  Transaction to Agent is a many-to-many relation

                   

                  If your users now search in FullName they will get all kinds of Client/Undertaker/Thirdparty)

                   

                  If  you also give them the option to choose Type they can (if they like) specify what they are searching for

                   

                  hth

                  /gg

              • Scripting help
                Jonathan Dienst

                Murali

                 

                Need som more info on your data model:

                 

                Are the UnderwriterID and Thirdparty different for the ClientID in the two tables? If they are, then I would rename them as something different in one of the two tables and link only on the ClientID.

                 

                Eg LOAD UndewriterID AS ClientUnderwriterID, Thirdparty AS ClientThirdparty....  for NameAddressData

                 

                If the ClientID can have transactions with many combinations of UnderwriterID and Thirdparty, (ie the key to the transaction table is ClientID, UnderwriterID and Thirdparty), then you need to decide whether you need the UnderwriterID and Thirdparty in the NameAddress table - ie what purpose do they serve?

                 

                If you need to link on all three fields (ie the key to the relationship is all three fields, you can allow QV to create a synthetic key, or create your own composite key. Another option is to merge the two tables to create a single fact table. The best option depends on your data model and desgin objectives.

                 

                Remember that QV creates an association between two tables by using a field of the same name in the two tables, so you would use:

                LOAD NameID as ClientID ....  for the NameAddress data

                 

                Hope these help

                Jonathan