Flag latest 3 occurence of field value

[didierodayo]

Hello,

I need some help figuring this out.

I want to flag the latest 3 occurence of a Year field as 1

And other occurence as 0.

Here is a sample table.

Load * inline [

Year, ID, Type

2016, v01, Fank

2015, v01, Fank

2014, v01, Fank

2013, v01, Fank

2012, v01, Fank

2016, v02, gasq

2015, v02, gasq

2012, v03, Tombi

2011, v03, Tombi

];

I want te resulting table to be

Year    ID    Type     Flag

2016        V01      Frank  1

2015   V01  Frank  1

2014   V01  Frank  1

2013   V01  Frank  0

2012   V01  Frank  0

2015  v02 gasq 1

2014  v02 gasq 1

2012    v03  Tombi  1

2011    v03  Tombi 1

Thanks

[Chanty4u]

AM not sure may be this

  =   FirstSortedValue(Dim,-dim,5)

[antoniotiman]

Hi Didier,

maybe this

Table:
Load * inline [
Year, ID, Type
2016, v01, Fank
2015, v01, Fank
2014, v01, Fank
2013, v01, Fank
2012, v01, Fank
2016, v02, gasq
2015, v02, gasq
2012, v03, Tombi
2011, v03, Tombi
];
Left Join LOAD ID,
If(Count(DISTINCT Year) >3,Max(Year,3),Max(Year,2)) as Flag
Resident Table
Group By ID;
NoConcatenate LOAD ID,Year,Type,If(Year >= Flag,1,0) as Flag
Resident Table;
Drop Table Table;

Regards,

Antonio

[ahaahaaha]

Hi,

May be like this

Table1:

Load * inline [

Year, ID, Type

2016, v01, Fank

2015, v01, Fank

2014, v01, Fank

2013, v01, Fank

2012, v01, Fank

2016, v02, gasq

2015, v02, gasq

2012, v03, Tombi

2011, v03, Tombi

];

NoConcatenate

Table2:

LOAD*,

If(Type=Previous(Type), Rangesum(1, peek('Sequence')), 1) as Sequence,

If(If(Type=Previous(Type), Rangesum(1, peek('Sequence')), 1)<4, 1, 0) as Flag

Resident Table1

Order By Type, Year desc;

DROP Table Table1;

Example at attached file.

Regards,

Andrey

[niclaz79]

Hi,

I would do this in two steps;

- First I would use

Temp:

LOAD *,

     autonumber(Year & ID & Type) as Occurence

Resident [TableName];

to set a value on each row from 1 to number of occurences.

- Then I would load the following:

Data:

LOAD

     *,

     if(Occurence <= 3, 1, 0) as Flag

Resident Temp;

drop field Occurence from Data;

[antoniotiman]

Hi Niclas,

good idea.

However You need Order By Desc and Change Autonumber Sintax, like this

Table:

Load * From ..;  // or Inline

Table1:
LOAD *,AutoNumber(RowNo(),ID) as Flag
Resident Table Order By ID,Year Desc;
NoConcatenate LOAD ID,Year,Type,If(Flag <= 3,1,0) as Flag
Resident Table1;

Drop Table Table,Table1;