Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help with left join

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!

1 Solution

Accepted Solutions
jason_michaelid
Luminary
Luminary

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

Hope this helps,

Jason

View solution in original post

8 Replies
edg_baltazar
Creator
Creator

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!

Not applicable
Author

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
Partner
Partner

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

Not applicable
Author

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_michaelid
Luminary
Luminary

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

Not applicable
Author

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_michaelid
Luminary
Luminary

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

Hope this helps,

Jason

View solution in original post

Not applicable
Author

Thank you Jason.

I have learned a lot from you sample code.

Really appreciate your help!