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

[Marijn]

Hi Daryn,

If you only need the batch number and the dates:

MvT281:
LOAD  
     [Entry Date]	as DATE281,
     Batch
FROM
[C:\Users\xxx\Downloads\Vehicles.xlsx]
(ooxml, embedded labels, table is Vehicles)
where MvT = 281;

MvT501:
OUTER JOIN (MvT281) LOAD  
     [Entry Date]	as DATE501,
     Batch
FROM
[C:\Users\xxx\Downloads\Vehicles.xlsx]
(ooxml, embedded labels, table is Vehicles)
where MvT = 501;

Hope it helps!

[Daryn]

Thanks Marijn,
I will have a look later today or tomorrow asap and come back with an update/mark as solution etc.

Appreciate the time you have taken,

Regards Daryn

[Daryn]

Hi Marijn,

Is there a way to set your script suggestion to only load the first date of either 281 or 501 (if it finds more than one of the same type), example the same batch number has been booked in twice on different dates (booked in, then out, then back in for warranty work) so has two entry dates with MvT 501 (but I only need the first)..... 

Hope that makes sense. I tried with Min ([Entry Date])  in both tables, but it just errored on load.

Again, appreciated.

Daryn

An example is this batch below, I want to calculate from the earliest booked in(581) of  04.11.2022 to the out (282) 26.01.2023 this should give me 83 days but the chart is -6 (the difference between the second 581 date and the 281.

[Marijn]

I think like this:

MvT281_load:
LOAD  
     [Entry Date]	as DATE281,
     Batch
FROM
[C:\Users\marijn\Downloads\Vehicles.xlsx]
(ooxml, embedded labels, table is Vehicles)
where MvT = 281;

main:
NOCONCATENATE LOAD FirstValue(DATE281)	AS DATE281,
	Batch
resident MvT281_load
group by Batch
order by DATE281 asc
; 

drop table MvT281_load;
//exit script;

MvT501_load:
LOAD  
     [Entry Date]	as DATE501,
     Batch
FROM
[C:\Users\marijn\Downloads\Vehicles.xlsx]
(ooxml, embedded labels, table is Vehicles)
where MvT = 501;


outer join (main)
LOAD FirstValue(DATE501) AS DATE501,
	Batch
resident MvT501_load
group by Batch
order by DATE501 asc
;

drop table MvT501_load;

[Daryn]

Marijn,

Again really appreciate your time and knowledge with this, I should be free to test this, this afternoon.  👍  🤞

Kindest Regards,

Daryn

[Daryn]

Marijn,

I have applied your load script and have no errors. But is still doesn't appear to apply the first date (in the example two dates for type '501'.

In my understanding of your script it should, but the entry date 04.11.2022 is being written to DATE501 as 01.02.2023 (the second date of type '501').

I am sorry to ask if you have any further ideas?

Thank you once again.

Regards, Daryn

[Marijn · Accepted solution]

Yes, the date was misinterpreted as something other than a date by Qlik. I've added the date# so it's interpreted correctly and then it can be ordered (with the order by) correctly. See here:

MvT281_load:
LOAD  
     floor(date#([Entry Date],'DD.MM.YYYY'))	as DATE281,
     Batch
FROM
[C:\Users\marijn\Downloads\Vehicles.xlsx]
(ooxml, embedded labels, table is Vehicles)
where MvT = 281;

main:
NOCONCATENATE LOAD FirstValue(DATE281)	AS DATE281,
	Batch
resident MvT281_load
group by Batch
order by DATE281 asc
; 

drop table MvT281_load;

MvT501_load:
LOAD  
     floor(date#([Entry Date],'DD.MM.YYYY'))	as DATE501,
     Batch
FROM
[C:\Users\marijn\Downloads\Vehicles.xlsx]
(ooxml, embedded labels, table is Vehicles)
where MvT = 501;


outer join (main)
LOAD FirstValue(DATE501) AS DATE501,
	Batch
resident MvT501_load
group by Batch
order by DATE501 asc
;

drop table MvT501_load;

Be aware that I also used a floor() to make the date into a number. If you put this into a table, make sure that your change this number to a date again with the number format settings.

[Daryn]

Marijn,

I applied your new loadscript (and thank you for the comment about floor). 

Unfortunately I still don't get the expected first date, which makes no sense to me!

Apologies, I am sure I have given you a headache as well as myself.

Kindest regards

Daryn

[Marijn]

@Daryn could you give me that dataset? I don't have the data entries I see in your example. It's strange because it works on my computer (I manually added the row to the data), so I'd like to test with that set if possible.