Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
What is the definition of Nochange and Change here?
there is change in status in Aug month from Done to inProgress
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))
));
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
If you need first row as "No Change" you could try this?
If(Status = Previous(Status) or RowNo() = 1, 'NoChange', 'Change') as StatusCheck
why u have used only function
Because Group By clause needs aggregation function like Only(),Min(),Max(),Avg() atc.
Thanks