topic Re: Load earliest date but when another field value matches a specific value. in App Development

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

Daryn — Tue, 07 Mar 2023 15:31:23 GMT

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

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

Marijn — Thu, 09 Mar 2023 14:23:12 GMT

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!

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

Daryn — Thu, 09 Mar 2023 14:31:42 GMT

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

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

Daryn — Fri, 10 Mar 2023 15:19:28 GMT

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.

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

Marijn — Mon, 13 Mar 2023 09:49:26 GMT

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;

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

Daryn — Mon, 13 Mar 2023 10:06:45 GMT

Marijn,

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

Kindest Regards,

Daryn

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

Daryn — Tue, 14 Mar 2023 12:50:29 GMT

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

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

Marijn — Tue, 14 Mar 2023 13:16:53 GMT

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.

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

Daryn — Wed, 15 Mar 2023 10:16:09 GMT

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

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

Marijn — Wed, 15 Mar 2023 10:46:48 GMT

@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.

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

Daryn — Wed, 15 Mar 2023 11:01:27 GMT

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

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

Marijn — Thu, 16 Mar 2023 08:12:10 GMT

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?

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

Daryn — Thu, 16 Mar 2023 08:19:21 GMT

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