Calculating Month from the end time

[andrea0901]

Hi All,

I need to calculate Month and Year (like Jul-2018) from End Time field.

where Maximum of ‘End Time’ for Invoice Doc No which is Terminated at all actions either Approver or Assign Approver.

CC	Invoice Doc No	Status	Start Time	End Time	Actions
SGX0	5254433680	Terminated	7/26/2018	7/31/2018	APPROVER
SGX0	5254433680	Terminated	7/11/2018	7/16/2018	APPROVER
SGX0	5254433680	Terminated	7/16/2018	7/21/2018	APPROVER
SGX0	5254433680	Terminated	7/21/2018	7/26/2018	APPROVER
SGX0	5254509489	Terminated	7/15/2018	7/17/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	7/17/2018	7/19/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	7/19/2018	7/21/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	7/21/2018	7/23/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	7/23/2018	7/25/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	7/25/2018	7/27/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	7/27/2018	7/29/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	7/29/2018	7/31/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	7/31/2018	8/2/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/2/2018	8/4/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/4/2018	8/6/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/6/2018	8/8/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/8/2018	8/10/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/10/2018	8/12/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/12/2018	8/14/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/14/2018	8/16/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/16/2018	8/18/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/18/2018	8/20/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/20/2018	8/22/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/22/2018	8/24/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/24/2018	8/26/2018	ASSIGN APPROVER
SGX0	5254509489	Terminated	8/26/2018	8/28/2018	ASSIGN APPROVER

example: In the above  for particular invoice doc 5254433680, the max of end time is 7/31/2018. so i need to derive this as max Month for each invoice doc. and for 5254509489 the max of end time is 8/28/2018. How to write the code in the script.

 Please help me.

[sunny_talwar]

Try this

Table:
LOAD * INLINE [
    CC, Invoice Doc No, Status, Start Time, End Time, Actions
    SGX0, 5254433680, Terminated, 7/26/2018, 7/31/2018, APPROVER
    SGX0, 5254433680, Terminated, 7/11/2018, 7/16/2018, APPROVER
    SGX0, 5254433680, Terminated, 7/16/2018, 7/21/2018, APPROVER
    SGX0, 5254433680, Terminated, 7/21/2018, 7/26/2018, APPROVER
    SGX0, 5254509489, Terminated, 7/15/2018, 7/17/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 7/17/2018, 7/19/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 7/19/2018, 7/21/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 7/21/2018, 7/23/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 7/23/2018, 7/25/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 7/25/2018, 7/27/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 7/27/2018, 7/29/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 7/29/2018, 7/31/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 7/31/2018, 8/2/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/2/2018, 8/4/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/4/2018, 8/6/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/6/2018, 8/8/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/8/2018, 8/10/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/10/2018, 8/12/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/12/2018, 8/14/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/14/2018, 8/16/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/16/2018, 8/18/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/18/2018, 8/20/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/20/2018, 8/22/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/22/2018, 8/24/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/24/2018, 8/26/2018, ASSIGN APPROVER
    SGX0, 5254509489, Terminated, 8/26/2018, 8/28/2018, ASSIGN APPROVER
];

Left Join (Table)
LOAD CC,
	 [Invoice Doc No],
	 Date(MonthStart(Max([End Time])), 'MMM-YYYY') as MonthYear
Resident Table
Where Match(Actions, 'APPROVER', 'ASSIGN APPROVER')
Group By CC, [Invoice Doc No];

[andrea0901]

Hi Sunny,

Thanks for replying. but when i load the same data with the below code i got the below response attached. 

my invoice doc no: 524433680 and the start time is 7/11/2018 and end time is 7/31/2018

so for this particluar invoice i need to derive DateMonthYear as 7/31/2018.

but the code is deriving as 01-Jul-2018 instead of 31-jul-2018

[sunny_talwar · Accepted solution]

You can change the script from the above to this

Left Join (Table)
LOAD CC,
	 [Invoice Doc No],
	 Date(Floor(MonthEnd(Max([End Time]))), 'M/D/YYYY') as MonthYear
Resident Table
Where Match(Actions, 'APPROVER', 'ASSIGN APPROVER')
Group By CC, [Invoice Doc No];

[andrea0901 · Accepted solution]

Thank you Sunny. Its working now.

[sunny_talwar]

Awesome