Load earliest date but when another field value matches a specific value.

[Daryn]

Hi all,

Thanks for reading this and assisting if possible.

I have the above load script below in place and working,

---------------------------------------------------------------------------------------------------
[Vehicles]:

LOAD
Material,
Quantity,
MvT,
Date#([Entry Date])
Batch,
FROM [lib://AttachedFiles/Vehicles.xlsx]
(txt, unicode, embedded labels, delimiter is '\t', msq) ;

---------------------------------------------------------------------------------------------------

But, the field 'Entry Date' contains all dates for vehicles of two movement types (MvT).

MvT = 501 (is a van booked in)

MvT = 281 (is a van booked out)

Now the issue, that some vehicles are booked in and out, more than once (coming back perhaps for warranty work) or human error in the booking process. 

I would just like to load the first date for each MvT, against the field 'Batch'  (this field remains the same against each movement type for each vehicle).

I guess I am looking for a way of applying MvT = '281'    or the '501' to something like the below?

Batch, Min([Entry Date]) AS DATE501,
Batch, Min([Entry Date]) AS DATE281

GROUP BY Batch;

I also tried to work it out using a couple of new variables;

If(MvT = '281', [Entry Date]) AS [Date Out],
If(MvT = '501', [Entry Date]) AS [Date In],

But couldn't work out the next step with that (peek or such) for this problem, b

I have sorted the example data attached by batch, just so you can see the same batch for both movement types (if it has been booked out).

Thanks in advance, again.

Regards Daryn

[Daryn]

Again, can't thank you enough for your time with this.

[Marijn]

Hi Daryn,

Also with the updated dataset I get the correct results, can you give me the complete script you're using? I see that there are fields in your example that are not used in my script, so maybe the group by or order by is changed with your additions?

[Daryn]

Hi Marijn,

Thank you for your reply. This prompted me to check and find an error in my loadscript (pointing to a previous copy of Vehicles.xlsx (it was looking at Vehicles_.xlsx)! An altered version I had tried some testing with.

Sorry to have not spotted this before and saved your last responses.

I would like to say a huge thank you again,  for your time and knowledge, I have leant a lot from this.

Really appreciated.

Kindest regards,

Daryn