Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have this table and I need filter only the rows with the biggest value in field "appear". Which was generated with autonumber function. How to do this ?
FIS3:
CROSSTABLE (Atribute,Value,28)
//NoConcatenate
Load
AutoNumber(Recno(), REF) as appear,
*
RESIDENT FIS
try below
FIS3:
CROSSTABLE (Atribute,Value,28)
//NoConcatenate
Load
AutoNumber(Recno(), REF) as appear,
*
RESIDENT FIS;
Inner join (FIS3)
LOAD REF,
max(appear)as appear
resident FIS3
group by REF;
@MT4T try below
FIS3:
CROSSTABLE (Atribute,Value,28)
//NoConcatenate
Load
AutoNumber(Recno(), REF) as appear,
*
RESIDENT FIS;
max:
LOAD max(num(fieldvalue('appear',recno()))) as max_appear
autogenerate fieldvaluecount('appear');
let vMax_Appear =peek('max_appear',0,'max');
drop table max ;
FIS4:
noconcatenate
LOAD *
RESIDENT FIS3
WHERE appear = '$(vMax_Appear)';
DROP TABLE FIS3;
Sorry, I need to load only max "appear" within subgroup "REF". Your solution load only one max "appear" top of all.
I believe there should be some sibling column to "appear" which shows max recno() per subgroup. And filter should look like this:
Where "appear" = "max_appear"
but i do not know how to do max(recno()) thing.
Do you need to consider any additional dimensions? Like the biggest "appear" for every customer?
try below
FIS3:
CROSSTABLE (Atribute,Value,28)
//NoConcatenate
Load
AutoNumber(Recno(), REF) as appear,
*
RESIDENT FIS;
Inner join (FIS3)
LOAD REF,
max(appear)as appear
resident FIS3
group by REF;
Thank you! I never thought that inner join could be used as a filter.
This data was related to products and unique serial numbers. Each number can appear multiple times on various stages during the return process. The goal was excluding some stations and operators. And out of this the pick last reliable scan to define actual status and demand for unique unit.