Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Edvin
Creator
Creator

Removing duplicate values after join

Hello, guys.
Need help, I've got one table in which is the essential information I need, and I've got another one, which I need from it, how much days a client used a rental car.

I tried to do left join on "Unique" key which is - Date (it's year and month "YYYYMM" format) and Plate number. Now the catch is, one car could have been on several accidents, for ex.: in 2019 May and 2019 June. But the client used rental car only for the 2019 May accident - everything perfect, as my "Unique" key is based on Date and Plate Number. But what happens, if any car have been in two accidents in the same month, for ex.: 2019 July - 2 accidents, and the client also used rental car for only first(or second) accident in that month, the Left Join give same value "Days used for rental car" for both those accidents, which is incorrect. But I get it - my unique key is both for the same accidents - which is 2019 July with the same plate number. So what I need, is just somehow remove the duplicate "Days for rental car" based on Date and Plate number. Not sure if I properly stated what I need, so if you need a "dummy data" - feel free to ask.

Here's my code below:

Table1:

LOAD

display_name,

calcdate,

year(floor(date(calcdate)))&num(month(floor(date(calcdate))))&plate_nr as Key,

plate_nr,

motorshop

FROM [lib://KKK/Detali*.xlsx]

(ooxml, embedded labels, table is Sheet)

where year(calcdate)>=2018;

 

Right Join(Table1)

LOAD

group,

motorshop,

FROM [lib://KKK/Motorshop names.xlsx]

(ooxml, embedded labels, table is Sheet1)

 

 

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

 

Left join (Table1)

LOAD *

Resident [Table2];

Drop Table [Table2];

 

 

Trying to summarise:

There are duplicate "Key" values for the Table1, because there can be several accidents for the same car in the same month, but it's the opposite for the Table2, there is a possibility that a client(we identify client by plate number) can take 2 times a rental car in the same month, but it's almost equal to zero, somehow if that's the case, Qlik Sense somehow identifies and returns the results perfect.

Any advice?

3 Replies
y_grynechko
Creator III
Creator III

Hey,

There is one question I have, what happens if the car was rented on the last day of month and the accident with the same plate number was on the 1st day of the next month? 

 

 

martinpohl
Partner - Master
Partner - Master

you want to join datas by month but they are assigned by date.

So why isn't it possible the join them by date instead of year and month?

You have accident date and calculation date.

another question:

why you want to join? It look likes that is possible only to link them by the Key-field.

Regards

Edvin
Creator
Creator
Author

Thanks guys for the replies.

Gonna be a little bit more clear.

First comes the accident, and then while we repair the damaged vehicle, we give them a rental car.

From the main excel, there is all info about the client and the car that it was damaged. The second excel I joined is all the information about "Why the rental car was given, at what time, at where exactly and for what damaged vehicle it was given(that's where the plate number pops up)"

My accident date with the date when was the rental car taken they wouldn't match perfectly. As you mentioned if the accident happened on the last day of the month and the rental car have been taken on the 1st day of the next month - well, I just have to give up on that data, because I can't exactly match them both. Because - The only thing "Unique" I have on what to match - is "Plate number", but as I mentioned above, i can't blindly just match on the plate number, because of the 2 accidents and only just 1 rental car.