Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
I'm sure there's a slightly cleaner way but it's late! See attached...
Hope this helps,
Jason
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!
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.
Concatenate the two tables first and then apply the rule. No left join needed this way.
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!
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
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!
I'm sure there's a slightly cleaner way but it's late! See attached...
Hope this helps,
Jason
Thank you Jason.
I have learned a lot from you sample code.
Really appreciate your help!