Value of Previous date, then assign status

[yvonne-c]

I am trying to look at the current record and the previous record

My data looks like this:

ID	Date	Head	Score
12	26-04-2016	1	1
12	26-04-2016	4	0
12	26-04-2016	7	0
12	26-04-2016	8	0
12	26-04-2016	10	0
12	26-04-2016	13	0
12	09-12-2015	1	1
12	08-08-2015	4	0
12	09-12-2015	7	0
12	09-12-2015	8	0

I have managed to get the previous date by creating the following in the script:

load farm_ID,

Head,

Date(Max(Date)) as MaxDate,

Date(Max(Date,2))as MaxDate2

Resident Mobility

Group by farm_ID, Head;

Now I need to get the score for the previous date in the next column.

I just cannot find the expression to make this work correctly, can anybody help?!

When this column is complete I want to assign a status based on the current and previous score.

So if the current score = 0 and the previous score = 0 the result would be 'Clear',

Any help appreciated!

[sunny_talwar · Accepted solution]

May be this?

Table:

LOAD ID,

    Date,

    Head,

    Score

FROM

[https://community.qlik.com/thread/215317]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  If(Score = 0 and Previous_Score = 0, 'Clear') as Flag;

LOAD ID,

  Date,

  Head,

  Score,

  If(ID = Peek('ID') and Head = Peek('Head'), Peek('Date')) as Previous_Date,

  If(ID = Peek('ID') and Head = Peek('Head'), Peek('Score')) as Previous_Score

Resident Table

Order By ID, Head, Date asc;

DROP Table Table;

[yvonne-c]

Thanks sunny, that works

I would like to add further conditions to the if statement, and put the result in the same 'flag' column, is that possible?

e.g.

If(Score = 2 and Previous_Score = 0, 'New') as Flag;

[sunny_talwar]

Ya that should be easy, right:

If(Score = 0 and Previous_Score = 0, 'Clear',

If(Score = 2 and Previous_Score = 0, 'New')) as Flag;

[yvonne-c]

Easy when you know how!

You're a star, thanks

[Ribeiro]

Hi Summy,
I would like to try to help me in the question below😅

If DateVnd and QtdeVnd and CodProdVnd,
and CodProdCmp and VlrCmp and Null DtCmp,
Bring the last one as shown in the image below:

DateVnd	QtdeVnd	CodProdVnd	CodProdCmp	VlrCmp	DtCmp
15/01/2020	1	410
15/01/2020	1	415
16/01/2020	2	410	800	0,15	16/01/2020
16/01/2020	2	415	801	0,16	16/01/2020
17/01/2020	1	410
17/01/2020	1	415
18/01/2020	9	410	800	0,11	18/01/2020
18/01/2020	9	415	801	0,17	18/01/2020

Correct Result:

[Ribeiro]

Fill in null values with the last value, as shown in the image.