topic Re: Previous Function in QlikView

Previous Function

Anonymous — Thu, 21 Sep 2017 06:01:19 GMT

Hi ,

I want to find previous of monthly value ,but data received on daily basis

Input

ID	MonthYear	Status	Date
1234	Jul-17	Done	7/31/2017
1234	Aug-17	InProgress	8/31/2017
1234	Sep-17	InProgress	9/18/2017
1234	Sep-17	InProgress	9/17/2017
1234	Sep-17	InProgress	9/16/2017
1234	Sep-17	InProgress	9/14/2017
1234	Sep-17	InProgress	9/13/2017
1234	Sep-17	InProgress	09/12/2017
1234	Sep-17	InProgress	09/10/2017
1234	Sep-17	InProgress	09/09/2017
1234	Sep-17	InProgress	09/07/2017
1234	Sep-17	InProgress	09/06/2017
1234	Sep-17	InProgress	09/05/2017
1234	Sep-17	InProgress	09/04/2017
1234	Sep-17	InProgress	09/03/2017
1234	Sep-17	InProgress	09/02/2017

Output.Required

ID	MonthYear	StatusCheck
1234	Jul-17	NoChnage
1234	Aug-17	Change
1234	Sep-17	NoChnage

Re: Previous Function

techvarun — Thu, 21 Sep 2017 06:13:54 GMT

What is the definition of Nochange and Change here?

Re: Previous Function

Anonymous — Thu, 21 Sep 2017 06:20:29 GMT

there is change in status in Aug month from Done to inProgress

Re: Previous Function

Anil_Babu_Samineni — Thu, 21 Sep 2017 06:21:19 GMT

May be this?

LOAD Num(ID) as ID,

MonthName(MonthYear) as MonthYear,

Status,

Date(Date) as Date,

If(Status = Previous(Status), 'NoChange', 'Change') as StatusCheck

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(

Remove(Row, Pos(Top, 22)),

Remove(Row, Pos(Top, 21)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 19)),

Remove(Row, Pos(Top, 18))

));

Re: Previous Function

antoniotiman — Thu, 21 Sep 2017 06:21:47 GMT

Hi,

Temp:
LOAD ID,
MonthName(MonthYear) as MonthYear,Only(Status) as Status,
Max(Date) as Date
FROM
https://community.qlik.com/servlet/JiveServlet/download/1348183-296465/Sample.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1)
Group By ID,MonthYear;
LOAD ID,MonthYear,
If(Status=Previous(Status) or RowNo()=1,'NoChange','Change') as StatusCheck
Resident Temp
Order By ID,MonthYear;
Drop Table Temp;

Regards,

Antonio

Re: Previous Function

Anil_Babu_Samineni — Thu, 21 Sep 2017 06:23:36 GMT

If you need first row as "No Change" you could try this?

If(Status = Previous(Status) or RowNo() = 1, 'NoChange', 'Change') as StatusCheck

Re: Previous Function

Anonymous — Thu, 21 Sep 2017 09:57:31 GMT

why u have used only function

Re: Previous Function

antoniotiman — Thu, 21 Sep 2017 10:03:57 GMT

Because Group By clause needs aggregation function like Only(),Min(),Max(),Avg() atc.

Re: Previous Function

Anonymous — Thu, 21 Sep 2017 10:16:17 GMT

Thanks