How do I get the first row of a group in the script.

[D19PAL]

Hi,

First of all sorry for starting a new post everytime (for some reason I can't reply, I keep getting HTML errors and when I preview the HTML, it still doesn't allow me to reply).

How do I get the first row of a group in the script.

Name         id        min_date            Date

John           1          01/01/2021        01/01/2021

John           1          01/01/2021        01/01/2021

John           2          01/01/2021        23/01/2021

John           2          01/01/2021        23/01/2021

Paul            3          01/01/2021        01/01/2021

Paul            3          01/01/2021        01/01/2021

Paul           4          01/01/2021        23/01/2021

Paul           4          01/01/2021        23/01/2021

Paul           4          01/01/2021        23/01/2021

Paul           4          01/01/2021        23/01/2021

Right what I want from this table and as another column is

* For each name and each Id to check if date is less or equal to min_date or above date, if less or above, only give me the answer in the first row, not on multiple ones like the example below.

Name         id        min_date            Date                               New Column

John           1          01/01/2021        01/01/2021                  Less

John           1          01/01/2021        01/01/2021

John           2          01/01/2021        23/01/2021                 Above

John           2          01/01/2021        23/01/2021

Paul            3          01/01/2021        01/01/2021                 less

Paul            3          01/01/2021        01/01/2021

Paul           4          01/01/2021        23/01/2021                   Above

Paul           4          01/01/2021        23/01/2021

Paul           4          01/01/2021        23/01/2021

Paul           4          01/01/2021        23/01/2021

Thanks

[joaopaulo_delco · Accepted solution]

Hi @D19PAL !

        I think the script below will solve your problem:

[TEMP]:
Load
    (Name & '|' & id) as KEY,*;
Load * Inline [
Name,id,min_date,DateM
John,1,01/01/2021,01/01/2021
John,1,01/01/2021,01/01/2021
John,2,01/01/2021,23/01/2021
John,2,01/01/2021,23/01/2021
Paul,3,01/01/2021,01/01/2021
Paul,3,01/01/2021,01/01/2021
Paul,4,01/01/2021,23/01/2021
Paul,4,01/01/2021,23/01/2021
Paul,4,01/01/2021,23/01/2021
Paul,4,01/01/2021,23/01/2021
];

NoConcatenate
[TABLE 1]:
Load *,
  RowNo() as order,
  if(isnull(KEY_Prev) or KEY_Prev<>KEY,
  if(DateM<=min_date,'Less', 'Above'),'') as New_Column;
Load 
    KEY,
    Previous(KEY) as KEY_Prev,
    Name,
    id,
    Date(min_date) as min_date,
    Date(DateM) as DateM
Resident TEMP order by Name,id;   

Drop Table TEMP;

[D19PAL]

Spot on, cheers.