Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Tenuki
Contributor III
Contributor III

Where clause in inner join not working

Hello, I have 3 tables link to my products that I would like to inner join to have only data for the current year.

As I don't have any date in table1 and table2, I try to add a where clause on the last table, on the 'CreationDate' field.

But when I display the data, I have all my data, the condition is not working.
This is very confusing, because when I load the data, the number of lines has decreased. 

My code look like this :
Table1:
ProductID
SupplierID;

Table2:
ProductID
ProductSalesCat1
ProductSalesCat2
INNER JOIN (Table1)
LOAD * RESIDENT Table2;

Table3:
ProductID
ProductName
CreationDate
YEAR(CreationDate) as YearCreationDate;
INNER JOIN (Table2)
LOAD * RESIDENT Table3
WHERE YEAR(CreationDate) = Year(Today());

I even try to drop Table1 and Table2 to clarify my scheme but it didn't help me.

Labels (1)
1 Solution

Accepted Solutions
anat
Master
Master

Table3:

load
ProductID
ProductName
CreationDate
YEAR(CreationDate) as YearCreationDate
WHERE YEAR(CreationDate) = Year(Today());

left join(Table3)

Table2:

load
ProductID
ProductSalesCat1
ProductSalesCat2;

left join(Table3)

Table1:

load
ProductID
SupplierID;

View solution in original post

2 Replies
anat
Master
Master

Table3:

load
ProductID
ProductName
CreationDate
YEAR(CreationDate) as YearCreationDate
WHERE YEAR(CreationDate) = Year(Today());

left join(Table3)

Table2:

load
ProductID
ProductSalesCat1
ProductSalesCat2;

left join(Table3)

Table1:

load
ProductID
SupplierID;

Tenuki
Contributor III
Contributor III
Author

Many thanks Anat, I see that I didn't understand that order is important, and that I should start from the biggest table and merge all on it.