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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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

 

Highlighted
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.

Asteroid

Thank you.

Labels