topic Re: Need help with left join in QlikView

Need help with left join

Tue, 10 Jul 2012 23:02:26 GMT

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!

Re: Need help with left join

edg_baltazar — Wed, 11 Jul 2012 00:58:56 GMT

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!

Re: Need help with left join

Wed, 11 Jul 2012 05:15:43 GMT

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.

Re: Need help with left join

giakoum — Wed, 11 Jul 2012 05:40:04 GMT

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

Re: Need help with left join

Thu, 12 Jul 2012 22:44:45 GMT

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!

Re: Need help with left join

Jason_Michaelides — Thu, 12 Jul 2012 22:51:51 GMT

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

Re: Need help with left join

Thu, 12 Jul 2012 23:08:22 GMT

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!

Re: Need help with left join

Jason_Michaelides — Thu, 12 Jul 2012 23:58:00 GMT

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

Hope this helps,

Jason

Re: Need help with left join

Fri, 13 Jul 2012 00:15:26 GMT

Thank you Jason.

I have learned a lot from you sample code.

Really appreciate your help!