Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In my script I have defined a table that loads in fields called account_nbr and production_date. I am limiting these field values that are loaded in by only loading in those where another field equals a certain value. This is what it looks like:
Table2:
LOAD
account_nbr
production_date
Resident Table1
where fieldName = 1;
My goal is to only load the first row for each account_nbr where fieldName = 1. Is this possible? Right now I am getting multiple rows loaded for each account_nbr since fieldName = 1 for more than one production_date. To put it more simply, I am only interested in loading the account_nbr and production_date values where the fieldName = 1 for the first time, and nothing after that. Thanks for the help.
Use this:
Table2:
LOAD
account_nbr
first(production_date) as production_date
Resident Table1
where fieldName = 1
Group by account_nbr;
Eduardo
Have you tried where exists()?
Try putting First 1 above the load.
How would this only select the top row for each account_nbr?
This only loads one row. I'm interested in loading the first row for each of several account numbers.
try with (in bold the most important piece of code)
table:
load rowno() as id, * inline [
account_nbr, production_date, fieldName
1,11,0
1,2,0
1,31,1
1,4,1
1,5,0
2,61,0
2,7,1
2,8,1
];
final:
NoConcatenate
load *
Resident table
where fieldName = 1
and peek(account_nbr) <> account_nbr
order by account_nbr, id;
DROP Table table;
EDIT: if you want the first by date, order by date instead of id
Use this:
Table2:
LOAD
account_nbr
first(production_date) as production_date
Resident Table1
where fieldName = 1
Group by account_nbr;
Eduardo