Data Manipulation

[EvansM]

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

[iversen_vasquez · Accepted solution]

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.

[EvansM · Accepted solution]

Thank you.