03-29-2016 10:27 AM
Acct pin last_updt cd Output
371520005 WKCQM0349867533 9/23/2011 X00
371520005 WKCQM0349867533 1/26/2014 X01 change
371520005 WKCQM0349867533 1/26/2014 X00 change
371520005 010488909014X99 1/8/2016 X97 change
371520005 010488909014X99 1/10/2016 X20 No change
371520005 010488909014X99 1/10/2016 X01 No change
371520005 WKCQM0349867533 1/10/2016 X00 change
371520005 010488909014X99 1/10/2016 X97 change
An account will have their pin updated.
if the first five characters of PIN is alpha then it is a valid pin, if not Invalid pin.
if combination of valid pin and cd is different from top row then there is a change (row1 and row2)
if there is change from valid Pin to invalid pin then there is change(row3 and row4)
if there is change in invalid pin and cd between rows then no change (row 4 and row5 and row5 and row6)
how to get summarized stats for an account:
No of Changes:5
No of distinct identifiers :2 (WKCQM0349867533 and 010488909014X99)
Distinct No of Invalid Pins:1 (010488909014X99)
Distinct No of Valid Pins:1 (WKCQM0349867533)
03-29-2016 11:13 AM
Tried this but doesnt seem to work as expected:
if anydigit(substr(pin,1,1))=1 then pin_ver='Digit';
if pin_ver='Alpha' and cats(pin,cd)=lag(cats(pin,cd) then chng='No Change';
if pin_ver='Digit' and pin=lag(pin) then chng='No Change';
03-29-2016 11:32 AM
Break your problem up and try each one at a time.
Your first requirement is 5 characters are alpha, so why does your substr formula look at only the first digit?
Also, post what your output should be.
You can use LAG to compare to the previous row.
Look at RETAIN for carrying values down through the ID.
03-29-2016 11:34 AM - edited 03-29-2016 11:58 AM
Lag and Dif are occasionally wonderful functions but understanding the value streams takes some work. Generally you will be much better off creating unconditional temporary variables (you drop them from the data set later) such as:
And use Lpin in your comparisons instead.
If pin = Lpin then do ...
And how does this affect your earlier question on "flip flop" of pin values?
03-29-2016 11:37 AM
Pin change will be super set of flip flop. Once the pin changes are identified there are some addistional rules to be plugged in to see flip flop criteria.