4 Replies Latest reply: Oct 28, 2009 7:34 PM by Miles Simpson RSS

    Newer User - Trying to Figure out a Join

    Miles Simpson

      I've got a pretty basic question, but since I don't know much about SQL, I'm kinda stuck. Basically I'm trying to join one table to another, but don't know how to do it properly. Any help would be much appreciated.

      I'll try to slim down the issue to it's most basic elements:

      Say I have an existing QlikView which is based around the table named "transaction". It has the fields "customer_number", "transaction_number", "transaction_date" and "transaction amount"

      The end-user now wants to add a new field to the existing table called "rating". The data will come from a table "customer_rating". It has the fields "customer_number_transaction", "rating_date" and "customer_rating". So the end-user wants to be able to see the existing list of transactions, but wants to see the rating of the customer at the time of the transaction.

      To start, I have matching customer numbers in either table "customer_number" <--> "customer_number_transaction". So I want to join the "rating" table to the "transactions" table... or just end up adding the the "customer_rating" field to the "transaction" table. It doesn't matter to me... whatever works.

      The problem is that a customer can receive multiple ratings over time... on 2001/01/01 they could have one rating, and then on 2009/01/01 they could have another. The actual transactions will happen on dates independant of the rating dates. So I essentially want to capture the "customer_rating" at the time of the "transaction_date".

      Any suggestions on how to go about Joining? Or any related examples I could refer to?

        • Newer User - Trying to Figure out a Join

          You can create a composite key between the two tables in your load script.

          E.g.

          customer_number & '#' & transaction_date as Key_Rating

          Create the field Key_Rating in both your tables and you don't have to join them. Qlikview will automatically relate the correct transactions. Make sure though that the composite key is constructed correctly though.

          /F

           

           

           

           

           

            • Newer User - Trying to Figure out a Join
              richardwanamaker

              So, what if you have this scenario:

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

              Table: CLAIMS
              ============
              PERSON_ID
              EFF_DT

              Data:
              PERSON_ID EFF_DT
              ====================
              000000001 2003-06-03

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

              TABLE: CATEGORY
              ====================
              PERSON_ID
              START_DT
              CATEGORY


              PERSON_ID START_DT CATEGORY
              ================================
              000000001 2000-01-01 A
              000000001 2001-01-01 B
              000000001 2002-01-01 C
              000000001 2003-01-01 D
              000000001 2004-01-01 E
              000000001 2005-01-01 F
              000000001 2006-01-01 G
              000000001 2007-01-01 A

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

              Now, you want to know what their actual applicable category was as of the Claim effective date, 2003-06-03 (which would be the Category application of 'D'). We want the most recent Category application as of or before that given date. With SQL, you would perform the following:

              Here's the query:
              SELECT CLAIMS.PERSON_ID, CLAIMS.EFF_DT, CATEGORY.CATEGORY
              FROM CLAIMS, CATEGORY
              WHERE CLAIMS.PERSON_ID = CATEGORY.PERSON_ID
              AND CLAIMS.EFF_DT >= (SELECT MAX(CATEGORY.START_DT)
              FROM CATEGORY
              WHERE CATEGORY.PERSON_ID = CLAIMS.PERSON_ID
              AND CATEGORY.START_DT < CLAIMS.EFF_DT) ;

              So, the question is, how do we do this within QLIKVIEW???

              Thanks in advance.

                • Newer User - Trying to Figure out a Join
                  prasad Somashekar

                  Hi,

                  You can try this method also to create one table, use the same sql in qlikview and run. This will create one table then you can easly work around in expression. Thanks!

                  SELECT CLAIMS.PERSON_ID as [Give some name], CLAIMS.EFF_DT as [ Give some name], CATEGORY.CATEGORY as [give some name]
                  FROM CLAIMS, CATEGORY
                  WHERE CLAIMS.PERSON_ID = CATEGORY.PERSON_ID
                  AND CLAIMS.EFF_DT >= (SELECT MAX(CATEGORY.START_DT)
                  FROM CATEGORY
                  WHERE CATEGORY.PERSON_ID = CLAIMS.PERSON_ID
                  AND CATEGORY.START_DT < CLAIMS.EFF_DT) ;

                  load the other tables also, then map it. Thanks!

                   

                  Regards,

                  Prasad

                    • Newer User - Trying to Figure out a Join
                      Miles Simpson

                      Thanks Prasad,

                      But I thought when creating tables in QlikView that you followed the convention:

                       

                      this_will_be_the_qlikview_table_name:

                      LOAD

                      source_field_1 AS [qlikview_field_1],

                      source_field_2 AS [qlikview_field_2]

                      ;

                      SQL SELECT *

                      FROM source_table

                      ;

                       

                      And from what I understood that the SQL portion wouldn't allow us to us the "AS" because it would be sent to our database via an ODBC connection we'd previously established. Are you saying that it should be possible to do the following?

                       

                      this_will_be_the_qlikview_table_name:

                      SQL SELECT source_field_1 AS [qlikview_field_1], source_field_2 AS [qlikview_field_2]

                      FROM source_table

                      ;

                      Or are you saying to load the tables as normal and create third table and use the suggested SQL query on a RESIDENT qlikview table?