How to implement a join with 'OR' condition

[#1]

Hi All,

Do you know how to join two tables from 2 different database using either one of the critiera.

I have two tables that one is from in Oracle Database and the other is from SQL Server

Oracle_Table

Call_ID,

Case_No,

Call_Time,

Oracle_Field_A,

Oracle_Field_B,

Oracle_Field_C,

...

SQL_Server_Table

Call_ID,

Case_No,

Call_Time,

SQLServer_Field_A,

SQLServer_Field_B,

SQLServer_Field_C,

...

Call_ID, Call_No, and Call_Time are supposed to be the same between the two table. However, it is not happened.  Sometimes the Call_ID is missing from one of the table and sometimes they have the same ID but the Call_Time is slightly different. Also the Case_No is not a unique number, It is unique for the same date only.

I have implemented the following script in MS-ACCESS to join the two table.

select *

from Oracle_Table ORA

Left join SQL_Server_Table SQL on

(

     ( ORA.Call_ID = SQL.Call_ID) OR

     (

          ORA.Case_No = SQL.Case_No and

          int(ORA.Call_Time) = int(SQL.Call_Time)

     )

)

Do you know how to implement this join in Qlikview?

Thanks!

[Anonymous]

very simple in QV, just identify Primary Key and then:

Oracle_Table

Call_ID,                //Assuming Call ID as primary key

Case_No,

Call_Time,

Oracle_Field_A,

Oracle_Field_B,

Oracle_Field_C,

...

left/Right/Outer/Inner(TableName)

SQL_Server_Table

Call_ID,                 //Assuming Call ID as primary key

Case_No,              ////////Rename Case No and Call Time as they will create synthetic key.

Call_Time,

SQLServer_Field_A,

SQLServer_Field_B,

SQLServer_Field_C,

...

One more thing, for me it seems both the table have same data so you can Concatenate as well if this is the case.

Make sure while concatenation field name of both the tables should be same.

[raghvendrasingh]

Hi Elim,

First take full join of both tables.

after that take resident of that table and apply these condition in where condition.

Hope this will give you solution.

Thanks,

RS

[#4]

Hi balrajahlawat ,

Thanks for your reply. However, I don't have the luck as you think. Call_ID is unique, but not primary key as it is not compulsory. Also the Oracle table didn't store the same information as the SQL_Server_Table so they can't be concatenated.

May be let me clarify my requirement further.

I need to request QlikView to join the two tables if they matched to either one of the following criteria.

A. Same Call_ID OR

B. Different Call_ID BUT, same Case_No and the date portion of the Call_Time timestamp field between the two tables are the same

This means for example

Oracle Table

Case_ID:        //Missing

Case_NO: 0001

Call_Time: '2015/01/01 01:00 AM'

Oracle_Field_A:111

SQL_Server

Case_ID: 100001A     ////Different Case_ID

Case_NO: 0001       //Same Case_No

Call_Time: '2015/01/01 01:55 AM' /////// Different Timestamp, but same day.

SQLServer_Field_A: A1

Than I am expecting that this two rows are for the same record and they need to be joined

Therefore, as you can see in my SQL statement in Access, I have to use the OR operator to force Access to join them by using either one of the two criteria.

Any Idea?

[raghvendrasingh]

Hi Elim,

If you applied the logic and got the answer then close the thread after making it correct /helpful answer.

else you can share qvw with some dummy data, we will help you.

Regards,

Raghvendra

[#6]

Hi raghvendra.singh,

Thanks for your response. My apologise that I may not clearly stated my requirement from my original statement, but I think your solution is close and will bring me on to the right track as I am thinking something similar as well.

May be let me clarify my requirement further.

I need to request QlikView to join the two tables if they matched to either one of the following criteria.

A. Same Call_ID OR

B. Different Call_ID BUT, same Case_No and the date portion of the Call_Time timestamp field between the two tables are the same

This means for example

Oracle Table

Case_ID:        //Missing

Case_NO: 0001

Call_Time: '2015/01/01 01:00 AM'

Oracle_Field_A:111

SQL_Server

Case_ID: 100001A     ////Different Case_ID

Case_NO: 0001       //Same Case_No

Call_Time: '2015/01/01 01:55 AM' /////// Different Timestamp, but same day.

SQLServer_Field_A: A1

Than I am expecting that this two rows are for the same record and they need to be joined

So what I am thinking is:

1, join the two tables using the criteria A (Same Call_ID)

2. Store this result

3. Reload the two tables into Qlikview, but this time, add a new column that only store the date portion of of the Call_Time. and join the two table again using the second criteria  (Different Call_ID BUT, same Case_No and the date portion of the Call_Time timestamp field between the two tables are the same)

4. Union the two result.

Do you think this way will work?

[jonathandienst · Accepted solution]

You need to take a few steps to perform the two joins. Attempting the above options will miss certain records (call IDs match but CallNo or CallTime do not) and could potentially duplicate others. I suggest the following:

1. Load the sources - add key fields for joins --

//------------------------------------------------------------------------------------------

// Load Oracle source

CONNECT ... to Oracle ...

Oracle_Table:

LOAD *,

  AutoNumber(Call_ID, 'key1') As Key1,

  AutoNumber(CaseNo & Call_Time, 'key2') As Key2;

SQL SELECT

  Call_ID,

  Case_No,

  Call_Time,

  Oracle_Field_A,

  Oracle_Field_B,

  Oracle_Field_C,

  ...

FROM OracleTable;

//------------------------------------------------------------------------------------------

// Load SQL Server source

CONNECT ... to SQL Server ...

Temp_SQL_Table:

LOAD *,

  AutoNumber(Call_ID, 'key1') As Key1,

  AutoNumber(CaseNo & Call_Time, 'key2') As Key2

  RowNo() As Sql.RowKey;

SQL SELECT

  Call_ID,

  Case_No,

  Call_Time,

  SQLServer_Field_A,

  SQLServer_Field_B,

  SQLServer_Field_C,

  ...

FROM SQLTable;

2. Do the two joins into temp key fields

//------------------------------------------------------------------------------------------

Left Join (Oracle_Table)

LOAD Key1,

  Sql.RowKey As RowKey1

Resident Temp_SQL_Table;

Left Join (Oracle_Table)

LOAD Key2,

  Sql.RowKey As RowKey2

Resident Temp_SQL_Table

WHERE Not(Exists(RowKey1, Sql.RowKey));

3. Compile the result in two steps (use inner joins to create two sub tables)

//------------------------------------------------------------------------------------------

// First key (Call_ID)

Result:

NoConcatenate:

LOAD *

Resident Oracle_Table;

Innner Join (Result)

LOAD Sql.RowKey As RowKey1,

  SQLServer_Field_A,

  SQLServer_Field_B,

  SQLServer_Field_C,

  ...

Resident Temp_SQL_Table;

//------------------------------------------------------------------------------------------

// Second key (Case_No,  Call_Time)

Result2:

NoConcatenate:

LOAD *

Resident Oracle_Table;

Innner Join (Result2)

LOAD Sql.RowKey As RowKey2,

  SQLServer_Field_A,

  SQLServer_Field_B,

  SQLServer_Field_C,

  ...

Resident Temp_SQL_Table;

4. Pull together

//------------------------------------------------------------------------------------------

// And together

Concatenate(Result)

LOAD * Resident Result2;

//------------------------------------------------------------------------------------------

// And unmatched rows from the oracle table

Concatenate(Result)

LOAD * Resident Oracle_Table

Where IsNull(RowKey1) And IsNull(RowKey2);

//------------------------------------------------------------------------------------------

// Clean up

DROP Tables Oracle_Table, Temp_SQL_Table, Result2;

DROP Fields RowKey1, RowKey2, Sql.RowKey, Key1, Key2;

[#8]

Hi jontydkpi,

I thinkyour solution lead me to the right track. But I don't know why I got a funky join result. Would be it be because I actually load the two tables from two different QVD files that was created from two different QlikView Worksheet file?

I read the book QlikView 11 For Developer mentioned

" It is important to note that the AutoNumber() function returns a number solely based on the load order. Encoding the same value in different QVW files might return different numbers. Therefore it is not possible to use results of the AutoNumber() function sourced from multiple QlikView documents."

Would it be resolved if I prepare both QVD files under the same QlikView Worksheet file and have the key stored in the corresponding QVD file proceed?

Thanks!

[#9]

Hi jonathan dienst,

I found the reason for my funny result now. It was because a typo from my code.

It works great now.

Thanks for your help!.

Elim.