topic Re: Recurrent Previous()? in QlikView

Recurrent Previous()?

Fri, 10 Jul 2015 19:24:45 GMT

Hi guys I am having a bit of a problem, I am trying to fill null rows by the last non null value. ill exemplify in a table.

This is what i have:

Ticket	Severity	status	date
AAA	-		01/01/2015 13:00
AAA	-		01/01/2015 14:10
AAA	SR		01/01/2015 15:20
AAA	-	Actioned	01/01/2015 16:00
AAA			01/01/2015 17:00
AAA	Sev1		01/01/2015 18:00
AAA	-	-	01/01/2015 19:00
BBB	-	-	02/01/2015 13:00
BBB	-	on Hold	03/01/2015 13:00
BBB	-	-	04/01/2015 13:00
BBB	-	-	05/01/2015 13:00
BBB	Sev2	Actioned	06/01/2015 13:00
BBB	-	-	07/01/2015 13:00

And this is what i want to achieve:

Ticket	Severity	status	date
AAA	SR	Actioned	01/01/2015 13:00
AAA	SR	Actioned	01/01/2015 14:10
AAA	SR	Actioned	01/01/2015 15:20
AAA	Sev1	Actioned	01/01/2015 16:00
AAA	Sev1	on Hold	01/01/2015 17:00
AAA	Sev1	on Hold	01/01/2015 18:00
AAA	-	-	01/01/2015 19:00
BBB	Sev2	on Hold	02/01/2015 13:00
BBB	Sev2	on Hold	03/01/2015 13:00
BBB	Sev2	Actioned	04/01/2015 13:00
BBB	Sev2	Actioned	05/01/2015 13:00
BBB	Sev2	Actioned	06/01/2015 13:00
BBB	-	-	07/01/2015 13:00

As you can see I want to obtain the previous value for each Ticket except for the last entry on each ticket, the last entry I will use a single value stored in a different table that is joined to this one by the ticket field and contains only 1 row.

Can someone please help? I can do this with previous function by loading the data in descending way but this only fills one previous null value and does no work when there is 2 and 3 null rows.

Many Thanks

Re: Recurrent Previous()?

hic — Fri, 10 Jul 2015 19:30:41 GMT

You're on the right track. Load the data descending as you suggest, and use Peek() instead of Previous(). Example:

If( Len(Trim(Severity))=0, Peek(Severity), Severity) as Severity

HIC

Re: Recurrent Previous()?

maxgro — Fri, 10 Jul 2015 19:43:46 GMT

LOAD

Ticket,

if(Severity='-' or len(trim(Severity))=0, null(), Severity) as Severity,

if(status='-' or len(trim(status))=0, null(), status) as status,

date

FROM

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

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

NoConcatenate load

Ticket,

date,

if(len(trim(Severity))=0 and Ticket=peek(Ticket), peek(Severity), Severity) as Severity,

if(len(trim(status))=0 and Ticket=peek(Ticket), peek(status), status) as status

Resident a

Order By Ticket, date desc;

drop table a;

Re: Recurrent Previous()?

Fri, 10 Jul 2015 19:49:19 GMT

Hi I have tried with peek this works only for the first empty row, the 2nd empty row does not get populated, also I have a condition which is "if(Ticket = previous(Ticket), ...." as it can only compare if the ticket is the same and this as obviously will leave my first row empty as the Ticket is not the same as the rpevious for the first record.

Many Thanks