Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Newer User - Trying to Figure out a Join

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?

4 Replies
Not applicable
Author

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

Not applicable
Author

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.

prasad
Contributor III
Contributor III

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

Not applicable
Author

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?