Select values with a where condition

[rafakmargos]

Hello, I have this table below:

Valor_ABS	Data_Usuario	Data_Analise	Value_1	Value_2	Value_3	Value_4
17	01/01/23 00:00:00	30/12/22 12:00:00	169,21	303	286	7
19	01/01/23 00:00:00	30/12/22 07:00:00	150,94	267	286	7
41	01/01/23 00:00:00	30/12/22 15:00:00	159,06	327	286	7
44	01/01/23 00:00:00	30/12/22 04:00:00	153,55	242	286	7
83	01/01/23 00:00:00	29/12/22 23:00:00	162,75	203	286	7
87	01/01/23 00:00:00	31/12/22 23:00:00	167,21	199	286	7
95	01/01/23 00:00:00	29/12/22 20:00:00	160,83	191	286	7
112	01/01/23 00:00:00	31/12/22 20:00:00	169,75	174	286	7
131	01/01/23 00:00:00	29/12/22 15:00:00	172,52	155	286	7
144	01/01/23 00:00:00	29/12/22 12:00:00	155,32	142	286	7
148	01/01/23 00:00:00	31/12/22 15:00:00	157,62	138	286	7
160	01/01/23 00:00:00	31/12/22 12:00:00	163,95	126	286	7
169	01/01/23 00:00:00	29/12/22 07:00:00	157,06	117	286	7
199	01/01/23 00:00:00	29/12/22 04:00:00	153,85	87	286	7
199	01/01/23 00:00:00	31/12/22 07:00:00	155,51	87	286	7
224	01/01/23 00:00:00	31/12/22 04:00:00	144,64	62	286	7
260	01/01/23 00:00:00	30/12/22 23:00:00	156,2	26	286	7
284	01/01/23 00:00:00	30/12/22 20:00:00	159,46	2	286	7

And I want to generate a new table based in the three rows with the maximum 'Valor_ABS' values.

In this example, I'd have a table like this:

224	01/01/23 00:00:00	31/12/22 04:00:00	144,64	62	286	7
260	01/01/23 00:00:00	30/12/22 23:00:00	156,2	26	286	7
284	01/01/23 00:00:00	30/12/22 20:00:00	159,46	2	286	7

How can I do this?

[BrunPierre · Accepted solution]

Hi @rafakmargos  As Valor_ABS increases numerically, you can try something like this to get the last three rows.

tmp:
LOAD *,
     RowNo() as Row#
FROM <SourceTable>;

LET vLastThreeRows = Peek('Row#', -3, 'tmp:');

NoConcatenate
[Last Three]:

LOAD *

Resident tmp

where Row# >= $(vLastThreeRows);

DROP Table tmp;

[sandeep-singh]

Try this

Let i =3;  // Change this as per your n number requirements

Test:
load * Inline [
Valor_ABS Data_Usuario Data_Analise Value_1 Value_2 Value_3 Value_4
17 01/01/23 00:00:00 30/12/22 12:00:00 169,21 303 286 7
19 01/01/23 00:00:00 30/12/22 07:00:00 150,94 267 286 7
41 01/01/23 00:00:00 30/12/22 15:00:00 159,06 327 286 7
44 01/01/23 00:00:00 30/12/22 04:00:00 153,55 242 286 7
83 01/01/23 00:00:00 29/12/22 23:00:00 162,75 203 286 7
87 01/01/23 00:00:00 31/12/22 23:00:00 167,21 199 286 7
95 01/01/23 00:00:00 29/12/22 20:00:00 160,83 191 286 7
112 01/01/23 00:00:00 31/12/22 20:00:00 169,75 174 286 7
131 01/01/23 00:00:00 29/12/22 15:00:00 172,52 155 286 7
144 01/01/23 00:00:00 29/12/22 12:00:00 155,32 142 286 7
148 01/01/23 00:00:00 31/12/22 15:00:00 157,62 138 286 7
160 01/01/23 00:00:00 31/12/22 12:00:00 163,95 126 286 7
169 01/01/23 00:00:00 29/12/22 07:00:00 157,06 117 286 7
199 01/01/23 00:00:00 29/12/22 04:00:00 153,85 87 286 7
199 01/01/23 00:00:00 31/12/22 07:00:00 155,51 87 286 7
224 01/01/23 00:00:00 31/12/22 04:00:00 144,64 62 286 7
260 01/01/23 00:00:00 30/12/22 23:00:00 156,2 26 286 7
284 01/01/23 00:00:00 30/12/22 20:00:00 159,46 2 286 7
](delimiter is ' ');

NoConcatenate
Test1:
load *,
AutoNumber(Valor_ABS) as flag
Resident Test
Order by Valor_ABS desc;

Drop Table Test;

NoConcatenate
final:
load *
Resident Test1
Where flag <= '$(i)';

drop table Test1;

[rafakmargos]

Hi @BrunPierre ,

It worked! Thank you very much!

At the last part of the script 

NoConcatenate
[Last Three]:

LOAD *

Resident tmp

where Row# >= $(vLastThreeRows);

 I just needed to LOAD every field manually and change the name of 1 field

If I use just 'LOAD *', it doens't load the table, because the new table have the same fields from the last one.

Is there other way to solve it?

[BrunPierre]

The last line of the script is a drop-table statement that removes the first table from the data model.