Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Spot on, cheers.