cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

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

 LOCATION CHECKDATE READ DAYS PAYDAY PLAYTYPE BILL CREDIT CHECK MYR 0000000006700500 190204 040628 0033 040725 02 00 00 1902 0000000006700500 190114 040729 0031 040825 02 00 00 1901 0000000006700100 190209 040827 0029 040925 02 00 00 1902 0000000006700100 190322 040929 0033 040925 02 00 00 1903 0000000006700100 041012 041027 0028 041129 02 00 00 0410

EXPECTED RESULTS

 LOCATION CHECKDATE READ DAYS PAYDAY PLAYTYPE BILL CREDIT CHECK MYR 0000000006700500 190204 040628 0033 040725 02 00 00 1901 0000000006700500 190114 040729 0031 040825 02 00 00 1812 0000000006700100 190209 040827 0029 040925 02 00 00 1901 0000000006700100 190322 040929 0033 041025 02 00 00 1902 0000000006700100 041012 041027 0028 041129 02 00 00 0409

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.

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