Joining tables

[Edvin]

Hello, guys,

I have a problem with Left join.

Table1:

LOAD
display_name,
date(floor(calcdate)) as Date,
Year(calcdate) & ceil(month(calcdate)/3) as Y/Quarter,
ceil(month(calcdate)/3) as Quarter,
num(month(floor([calcdate]))) as "Month",
year(floor(calcdate)) as "Year",
year(floor(date(calcdate)))&num(month(floor(date(calcdate))))&plate_nr as Key,
plate_nr,
FROM [lib://KKK/Detali*.xlsx]
(ooxml, embedded labels, table is Sheet)
where year(calcdate)>=2018;

Table2:

Left join (Table1)
LOAD
year(date("accident_date"))&num(month(date("accident_date")))&"reg_plate" as Key,
"Rental_car"

FROM [lib://KKK/Trail*.xlsx]
(ooxml, embedded labels, table is Sheet2)
where "Rental_car"<>0;

I've created a Key based on Year, month and plate number. Plate numbers are Unique, but they can appear in, for example, 2018 08 and later on 2019 01, so I need to join on dates too. So the problem here is, that it only joins on 2018 year. After the join i can only find "Rental_car" values for 2018 year, but not for the 2019. I know for sure, that it should be values for the 2019 values, too. Next, if for the Table2 I only take from the 2019 excel file (not from the 2018-2019), then I can see those values for "Rental_car", so what the hell is this? Why it's only joining on the lowest year of the data, but not for all..?

Sorry for my english, if i wasn't clear enough I can try to explain more.

Thank you.

[y_grynechko]

Table1:

LOAD
display_name,
date(floor(calcdate)) as Date,
Year(calcdate) & ceil(month(calcdate)/3) as Y/Quarter,
ceil(month(calcdate)/3) as Quarter,
num(month(floor([calcdate]))) as "Month",
year(floor(calcdate)) as "Year",
year(floor(date(calcdate)))&num(month(floor(date(calcdate))))&plate_nr as Key,
plate_nr,
FROM [lib://KKK/Detali*.xlsx]
(ooxml, embedded labels, table is Sheet)
where year(calcdate)>=2018;

Table2:

LOAD
year(date("accident_date"))&num(month(date("accident_date")))&"reg_plate" as Key,
"Rental_car"

FROM [lib://KKK/Trail*.xlsx]
(ooxml, embedded labels, table is Sheet2)
where "Rental_car"<>0

and where exists(Key, year(date("accident_date"))&num(month(date("accident_date")))&"reg_plate");

Can you try this and let me know if that gives you better results? 

[Edvin]

Hey, thanks for the help.

But nothing changed. I don't know what's causing this issue, but I am loading Table2, from three excel different year files, and what it seems, that it only takes from the first excel file which is 2018.xlsx. I've still got 2019 1H.xlsx and 2019 2H.xlsx, if I try to load for only 2019 year like FROM 2019 ??.xlsx, it only takes from the first excel file which is 2019 1H.xlsx..
I really hope that you will understand my poorly english explanation skills

[Edvin · Accepted solution]

Ah, morning is wiser than evening, as we say.

Googled it properly and found the solution, if anyone come up with the same problem I'll leave it here:

https://community.qlik.com/t5/New-to-QlikView/Left-Join-of-3-tables-stored-in-multiple-files/td-p/74...