community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Data Manipulation

Asteroid

 

Hello,

 

Please refer the example below. I need to fix data sample table to Expected result table.

 

This is what I want  CHECKMRY = Month and year of CHECKDATE except if Cycle = 500 (last 3 digits of location, example: if location is 0000000006700100, then the cycle is 100.  0000000006700500, then cycle is 500) then the CHECKMRY will be the previous Month and year.  If an account is a 500 cycle, then if the CHECKDATE is 02-04-19 then the CHECKMRY is 1901.   If the CHECKDATE = 01-14-19 then the CHECKMRY is 1812.

 

CHECKDATE  format is YYMMDD and I want to be YYMM  

 

Any help I will appreciate!

 

Thanks 

 

 

 

 

 

 

 

DATA SAMPLE

LOCATIONCHECKDATEREADDAYSPAYDAYPLAYTYPEBILLCREDITCHECK MYR
000000000670050019020404062800330407250200001902
000000000670050019011404072900310408250200001901
000000000670010019020904082700290409250200001902
000000000670010019032204092900330409250200001903
000000000670010004101204102700280411290200000410

 


EXPECTED RESULTS

LOCATION CHECKDATEREADDAYSPAYDAYPLAYTYPEBILLCREDITCHECK MYR
000000000670050019020404062800330407250200001901
000000000670050019011404072900310408250200001812
000000000670010019020904082700290409250200001901
000000000670010019032204092900330410250200001902
000000000670010004101204102700280411290200000409

 

Meteoroid

Hi,

 

My formulas might be messy but I hope I answered your question. I just parsed the year and month from the CHECKDATE, then defined a "Month v2" and "Year v2" which applies the logic of having the CYCLE = 500 where the previous monthyear will be returned. See below formulas.

 

formulas 1.PNGformulas 2.PNG

 

 

Note that rows 3 to 5 do not have a cycle = 500 so their month-year have been retained.

Attached is the workflow I created. Hope this helps.

Highlighted
Asteroid

Thank you.

Labels