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?
You can create a composite key between the two tables in your load script.
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.
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???
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) ;
But I thought when creating tables in QlikView that you followed the convention:
source_field_1 AS [qlikview_field_1],
source_field_2 AS [qlikview_field_2]
SQL SELECT *
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?
SQL SELECT source_field_1 AS [qlikview_field_1], source_field_2 AS [qlikview_field_2]
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?