topic Load multiple rows with the same minimum date per value in another cell in QlikView

Load multiple rows with the same minimum date per value in another cell

sammo — Sat, 16 Nov 2024 21:21:42 GMT

hello community

i want to load data like from a table like this:

ID, Name, Number, Date

485, Peter, 2, 2019-01-17
485, Marc, 22, 2019-01-17
485, Marc, 64, 2019-01-17
485, Peter, 452, 2019-01-18
485, Jessica, 2, 2019-01-18
485, Sarah, 2, 2019-01-18
485, Jessica, 2, 2019-01-19
490, Marc, 2, 2019-02-02
490, Jessica, 2, 2019-02-02
490, Sarah, 2, 2019-02-02
490, Jessica, 2, 2019-02-03
490, Marc, 2, 2019-02-08
490, Peter, 2, 2019-02-08
495, and so on...

so i want to load every min(Date) row from every ID.

So I'll have data like:
485, Peter, 2, 2019-01-17
485, Marc, 22, 2019-01-17
485, Marc, 64, 2019-01-17
490, Marc, 2, 2019-02-02
490, Jessica, 2, 2019-02-02
490, Sarah, 2, 2019-02-02
495, and so on...

I'm glad if someone can help me with this.

Thank you!

Re: Load multiple rows with the same minimum date per value in another cell

dplr-rn — Fri, 22 Feb 2019 18:11:07 GMT

How do you want the final table to look like.

try something like below

Temp:
load * Inline
[

ID, Name, Number, Date

485, Peter, 2, 2019-01-17
485, Marc, 22, 2019-01-17
485, Marc, 64, 2019-01-17
485, Peter, 452, 2019-01-18
485, Jessica, 2, 2019-01-18
485, Sarah, 2, 2019-01-18
485, Jessica, 2, 2019-01-19
490, Marc, 2, 2019-02-02
490, Jessica, 2, 2019-02-02
490, Sarah, 2, 2019-02-02
490, Jessica, 2, 2019-02-03
490, Marc, 2, 2019-02-08
490, Peter, 2, 2019-02-08
];

load ID, Name, Number, Min(Date) as MinDate
resident Temp
group by ID, Name, Number;

drop table Temp;

Re: Load multiple rows with the same minimum date per value in another cell

sammo — Tue, 26 Feb 2019 09:00:17 GMT

Thank you for your feedback!

This works mostly well 🙂

How can i change your script, if I just want to load only the min-Dates from every ID? except all other data.

I want to have all the fields, but the Name and Number should be ignored while i try to select the min dates.

The result should be:

485, Peter, 2, 2019-01-17
485, Marc, 22, 2019-01-17
485, Marc, 64, 2019-01-17
490, Marc, 2, 2019-02-02
490, Jessica, 2, 2019-02-02
490, Sarah, 2, 2019-02-02

is it possible to do this?