Need help with left join

[#1]

I have two tables A & B

Here is the code:

Table_A:

Load Distinct *

From SourceA

Load DIstinct *

Table_B:

From SourceB

Then I will to do the left join, based on criteria.

So I am writing

Left Join (Table_B)

If(Table_A.ID > Table_B.ID, Table_A.Name, null()) as Name

Resident Table_B

QlikView is telling that it could not know where the field ID Table_B.ID is from. Anyone has idea what's wrong?

Thanks a lot!

[edg_baltazar]

Try to  create the new field after the left join, something likes this....

Table_T:

Load Distinct *

From SourceA

Left Join

Load DIstinct *

From SourceB

Table:

Noconcatenate

Load *,

If(Table_A.ID > Table_B.ID, Table_A.Name, null()) as Name

Resident Table_T

Drop Table Table_T;

Let me know if this help you, regards!

[#3]

Thanks Edgar.

However, I had the same issue. Table A & B are from different database, so they can't be linked to each other directly.

Table_A:

Load Distinct *

From SourceA

Table_B:

Load DIstinct *

From SourceB

Table:

Noconcatenate

Load *,

If(Table_A.ID > Table_B.ID, Table_A.Name, null()) as Name

Resident Table_A

It's still saying it could not find field Table A.ID.

[giakoum]

Concatenate the two tables first and then apply the rule. No left join needed this way.

[#5]

It still doesn't work.

Does anyone have idea how to join tables with conditions? I have searched the entire discussion board, and tried several ways, but none of the methods worked.

Thanks!

[Jason_Michaelides]

Please provide the field names for Tables A and B. What are the common fields? Your join doesn't make much sense in any language I think - it looks like you're joining table B onto itself but using some bits of table A. Can you explain full in English (not SQL code) what you're tryin to achieve?

Jason

[#7]

Hi Jason,

Thanks for the ideas.

This is full explation of the problem.

Table_A:

Load Distinct *;

select A_ID, X1, X2, X3, Begin_Date, End_Date

From SourceA

Table_B:

Load DIstinct *;

select B_ID, Y1, Y2, Effect_Date

From SourceB

I want to merge A with B with the conditions where:

1. Table_A.A_ID = Table_B.B.ID

2. Table_B.Effect_Date between Table_A.Begin_Date and Table_A.End_Date

After merge, I want to get a new table with columns

B_ID, Y1, Y2, Effect_Date, X1, X2, X3

Thanks!

[Jason_Michaelides · Accepted solution]

I'm sure there's a slightly cleaner way but it's late!  See attached...

Hope this helps,

Jason

[#9]

Thank you Jason.

I have learned a lot from you sample code.

Really appreciate your help!