Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
HI guys,
I think i need your help on a new developpment I try to do on my datas.
I have two tables with data related to my business :
The Program table is what I have sold to my client. He has reserved some slots on my production calendar for a precise date, operation and vehicle type.
The Program table looks like this :
Vehicle type | Operation | Site | Date |
E71 | ATPMC | B.F.C. | 17/01/2018 |
E71 | ATPMC | B.F.C. | 22/01/2018 |
E71 | ATPMC | B.F.C. | 08/02/2018 |
E71 | ATPMC | B.F.C. | 19/03/2018 |
E71 | ATPMC | B.F.C. | 19/04/2018 |
E71 | ATPMC | B.F.C. | 11/06/2018 |
The Realization table is what the client has delivered to us. In this second table there is the vehicle number and its type, operation, site and date of delivery.
In my contract the slot is reserved for 30 days, so the client can deliver its vehicle in a -15 / +15 shift. What i'm trying to do is to add a column in the Program table who will show what vehicle number takes the slot, and one in the Realization table which will show the date of the slot taken. (Empty if no slot taken)
The realization table looks like this :
Immatriculation | Vehicle type | Operation | Site | Date |
118739017051 | R80 | ATPMC | N.P.D.C. | 08/01/2018 |
118739024560 | R80 | ATPMC | N.P.D.C. | 09/01/2018 |
118739026144 | R80 | ATPMC | Lorraine | 10/01/2018 |
118739026227 | R80 | ATPMC | Alsace | 05/01/2018 |
118739026482 | R80 | ATPMC | Lorraine | 10/01/2018 |
A few rules : the first vehicle to enter the -15 / +15 shift on a slot is taken the slot. Of course only one vehicle can enter a slot.
I've tried to do this but i always finish on a circular depedency... Someone could help ?
@Anonymous,
Here is an example for your reference.
Thanks but I dont understand how to use the MAXX function to solve my problem.
Only one vehicle can occupy one slot, and I can't figure how to achieve this...
Here is an example to make it clear, i made a crossjoin between my slot table and entry table.
The slot 42 is reserved on the NPDC site, for an ATPSUR operation on a S58 type the 06/01/2018. So it can be taken by a vehicle entered between 22/12/2017 and 21/01/2018. I have 11 vehicles that have entered my site between these two dates, so the first one will take the slot, the immatriculation 318747721013.
It's easy because its the first slot for this site, operation and vehicle type. But for the second one, the slot number 50, i would like to find a way to exclude the 318747721013 vehichle because it took the first slot, and say that this slot is taken by the vehicle 318747710172.
So for each slot the result depends of the results of the past slots, it's looks like a loop and i dont know how to code this. Anyone on this ?
I'm lost and it's a very important feature for me 🙂
Link to the full tables : https://www.dropbox.com/sh/fry8kn9fpjeotyo/AABiIcWbJHFlLdmpB6XeamSNa?dl=0
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |