Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
sammo
New Contributor

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

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!

Labels (3)
2 Replies
Partner
Partner

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

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;
sammo
New Contributor

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

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?