topic Re: Load only last status! in QlikView

Load only last status!

Fri, 31 Jan 2014 10:50:35 GMT

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:

Re: Load only last status!

Fri, 31 Jan 2014 10:51:43 GMT

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

Re: Load only last status!

Fri, 31 Jan 2014 10:54:44 GMT

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)

Re: Load only last status!

Fri, 31 Jan 2014 10:58:18 GMT

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
;

Re: Load only last status!

maxgro — Fri, 31 Jan 2014 11:02:53 GMT

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;

Re: Load only last status!

MK_QSL — Fri, 31 Jan 2014 11:04:48 GMT

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.

Re: Load only last status!

maxgro — Fri, 31 Jan 2014 11:06:11 GMT

and in chart

dimension COD

NAME

expression FirstSortedValue(STATUS, -ROW)

Re: Load only last status!

Fri, 31 Jan 2014 11:07:04 GMT

if you do this in chart --

then try this

Dimension1-

Aggr(max(Row),Name)

then

Dimension2-

COD

Dimension3-

Name

Expression-

Status

Re: Load only last status!

Fri, 31 Jan 2014 11:09:56 GMT

try this

Dimension1-

COD

Dimension2-

NAME

Expression

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

Re: Load only last status!

Fri, 31 Jan 2014 11:21:06 GMT

Perfect! Works great!

Thank You Massimo and all of You!

Re: Load only last status!

Fri, 31 Jan 2014 11:22:24 GMT

Nice. Works like a charm.

Thanks again!