Retrieve Rows where a change in values (intwo columns) occured

Database
Fan

Retrieve Rows where a change in values (intwo columns) occured

Hi all,I will try to explain this in the best way possible.
I have a dataset and I am trying to retrieve the only the rows where a change has occurred. I have tried QUALIFY but I can't get my head around it.
For example, looking at the following,
I would like to retrieve the row(s) prior and after the change depending on CPP and GRP grouped by CLIENT ID AND PLATFID. So in essence if there was a change in CPP and/or GRP for a given CLIENTID and PLATFID, bring back the rows that changed.

CLIENTID PLATFID CPP GRP MONTH
1,234 23,528,397 NCC NW0002222 01/02/2014
1,234 23,528,397 NCC NW0002222 01/03/2014
1,234 23,528,397 NCC NW0002222 01/04/2014
1,234 23,528,397 NCC NW0002222 01/05/2014
1,234 23,528,397 NCC NW0002222 01/06/2014
1,234 23,528,397 NCC NW0002222 01/07/2014
1,234 23,528,397 NCC NW0002222 01/08/2014
1,234 23,528,397 NCC NW0002222 01/09/2014
1,234 23,528,397 NCC NW0002222 01/10/2014
1,234 23,528,397 NCC NW0002222 01/11/2014
1,234 23,528,397 NCC NW0002222 01/12/2014
1,234 23,528,397 NCC NW0002222 01/01/2015
1,234 23,528,397 NCC NW0002222 01/02/2015
1,234 23,528,397 NDD NW0002333 01/03/2015 <-- Row to retrieve
1,234 23,528,397 NDD NW0002333 01/04/2015 <--Row to retrieve
1,234 23,528,397 NDD NW0002333 01/05/2015
1,234 23,528,397 NDD NW0002333 01/06/2015
1,234 23,528,397 NDD NW0002333 01/07/2015
1,234 23,528,397 NDD NW0002333 01/08/2015
1,234 23,528,397 NDD NW0002333 01/09/2015
1,234 23,528,397 NDD NW0002333 01/10/2015
1,234 23,528,397 NDD NW0002333 01/11/2015
1,234 23,528,397 NDD NW0002333 01/12/2015
1,234 23,528,397 NDD NW0002333 01/01/2016 <--Row to retrieve
1,234 23,528,397 AMM NW0004588 01/02/2016 <--Row to retrieve
1,234 23,528,397 AMM NW0004588 01/03/2016
1,234 23,528,397 AMM NW0004588 01/04/2016
1,234 23,528,397 AMM NW0004588 01/05/2016
1,234 23,528,397 AMM NW0004588 01/06/2016

Your helps would be appreciated.

Thanks,
Marios
Tags (1)
3 REPLIES
Junior Contributor

Re: Retrieve Rows where a change in values (intwo columns) occured

Your example is not showing the correct rows to be returned.

You're searching for changes in two columns and two directions (previous/next row), thus you need multiple OLAP functions:

qualify
min(CPP) -- lag
over (partition by CLIENTID, PLATFID
order by MONTH
rows between 1 preceding and 1 preceding) <> CPP
or
min(CPP) -- lead
over (partition by CLIENTID, PLATFID
order by MONTH
rows between 1 following and 1 following) <> CPP
or
min(GRP) -- lag
over (partition by CLIENTID, PLATFID
order by MONTH
rows between 1 preceding and 1 preceding) <> GRP
or
min(GRP) -- lead
over (partition by CLIENTID, PLATFID
order by MONTH
rows between 1 following and 1 following) <> GRP
Fan

Re: Retrieve Rows where a change in values (intwo columns) occured

Hi Dieter,

Thank you so much that worked!

as mentioned the rows to be retrieved were wrong; these are the correct ones:

1,234 23,528,397 NCC NW0002222 01/02/2015
1,234 23,528,397 NDD NW0002333 01/03/2015
and
1,234 23,528,397 NDD NW0002333 01/01/2016
1,234 23,528,397 AMM NW0004588 01/02/2016

Re: Retrieve Rows where a change in values (intwo columns) occured

Hi,

Please have a look at below solution. Hope this is working as per your requirement.

SELECT 

CLIENTID,PLATFID,           CPP,   GRP,  MONTH1 FROM 

(

SELECT

CLIENTID,PLATFID,           CPP,   GRP,  MONTH1,

PREVIOUS,

PREVIOUS1,

MIN(PREVIOUS) OVER(ORDER BY MONTH1 ROWS UNBOUNDED PRECEDING) FIRST1,

MIN(PREVIOUS1) OVER(ORDER BY MONTH1 ROWS BETWEEN CURRENT ROW AND  UNBOUNDED FOLLOWING) LAST1

FROM

(

SELECT CLIENTID,PLATFID,    CPP,   GRP,  MONTH1,

COALESCE(MAX(GRP) OVER (PARTITION BY GRP ORDER BY CLIENTID,PLATFID,CPP,GRP,MONTH1 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),'~')

AS PREVIOUS,

COALESCE(MAX(GRP) OVER (PARTITION BY GRP ORDER BY CLIENTID,PLATFID,CPP,GRP,MONTH1 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),'~')

AS PREVIOUS1

FROM

TEST.TEST_SCENARIO2

)A

WHERE 

PREVIOUS='~'

OR PREVIOUS1='~'

)A

WHERE 

FIRST1<>'~'  AND LAST1 <>'~'

ORDER BY 1,2,3,4,5;

Thanks,

Deepak Arora

DARORA