Load only last status!

[#1]

Hi!

I need to load only the latest status of the same name and user code.

In short, the last line pertinent to an id.

This can be done both in the script and in an expression?

Thank You!

I have this:

I want this:

[#2]

Dont you have any kind of Date to define wich is the last status?

[#3]

Oh just saw the row_id....

guessing that's is incremental i would use a resident load on the script to flag the last status (so you keep the history of change status)

[#4]

Use max and group by

Data:

LOAD
max(Row) as Row,
COD,
NAME,
STATUS
FROM Data.xlsx
(ooxml, embedded labels, table is Sheet1)
Group By COD,
NAME,
STATUS
;

[maxgro · Accepted solution]

in script, assuming the last by rn (= your row field) and using peek to filter

Tmp:

load *, rowno() as rn;

LOAD * INLINE [

cod, name, status

1, j, a

5, m, a

1, j, i

1, j, a

3, c, a

3, c, i

5, m, i

1, j, i

];

Table:

NoConcatenate

load

  rn, cod, name, status

Resident Tmp

where peek(cod) <> cod or peek(name) <> name

order by cod, rn desc;

drop table Tmp;

[MK_QSL]

Make a Straight Table

Dimension = Name and Cod

Expression = FirstSortedValue(STATUS,-Row)

Make a Straight Table

Dimension = Name

Expression = Aggr(FirstSortedValue(STATUS,-Row),COD)

Make a Straight Table

Dimension = Code

Expression = Aggr(FirstSortedValue(STATUS,-Row),Name)

Hope this would help.

[maxgro]

and in chart

dimension           COD

                         NAME

expression          FirstSortedValue(STATUS, -ROW)

[#8]

hi

if you do this in chart --

then try this

Dimension1-

Aggr(max(Row),Name)

then

Dimension2-

COD

Dimension3-

Name

Expression-

Status

[#9]

hi

try this

Dimension1-

COD

Dimension2-

NAME

Expression

Aggr(Firstsortedvalue(Status,-Row),NAME)

[#10]

Perfect! Works great!

Thank You Massimo and all of You!