Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sammo
Contributor
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
dplr-rn
Partner - Master III
Partner - Master III

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
Contributor
Contributor
Author

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?