Logic

[panipat1990]

Hi Team,

I Stuck a requirement. i have a table which contain 2 column ..Column 1 is employee code.and column 2 is date of confirm.

Date of confirm field contain a lot of dates.For this i am sharing data..My Requirement is if date of confirm  not equal to monthend.then employee code count on previous month..If Equal to monthend then it be same remain.Show in below table...Header3 is my reasult Field.....suppose my confirm date is 15-07-2016..then it should be count on 30-06-2016.

Note:- in data NULL Means Today

Header 1	Header 2	Header 3
Employee Code	Date of Confirm	Result(New Field)
1	Null	Null
2	15-07-2016	30-06-2016
3	25-09-2015	31-08-2015
4	01-04-2016	31-03-2016
5	31-07-2016	31-07-2016
6	19-03-2015	28-02-2015
7	28-02-2014	28-02-2014
8	Null	Null

[othniel2014]

something like that?

Table:

LOAD Emp_code,

     [Date Of Confirm],

     if(Floor(num([Date Of Confirm])) = Floor(num(MonthEnd([Date Of Confirm]))), [Date Of Confirm], MonthEnd(AddMonths([Date Of Confirm],-1))) as Result

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Edit: if you want to change NULL for today

Table:

LOAD

     Emp_code,

     [Date Of Confirm],

     if([Date Of Confirm] like 'NULL',

          Today(),

          if(Floor(num([Date Of Confirm])) = Floor(num(MonthEnd([Date Of Confirm]))),

               [Date Of Confirm],

               MonthEnd(AddMonths([Date Of Confirm],-1))

          )

     ) as Result

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

[effinty2112]

Hi Gourav,

Try this in the script:

Data:

LOAD

Emp_code,

[Date Of Confirm],

Date(MonthEnd(AddMonths([Date Of Confirm],if(Floor(MonthEnd([Date Of Confirm])) = floor([Date Of Confirm]),0,-1))),'MMM-YYYY') as MonthYear;

LOAD

Emp_code,

if( [Date Of Confirm] = 'NULL',Today(),[Date Of Confirm]) as [Date Of Confirm];

LOAD Emp_code,

     [Date Of Confirm]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Then you can create this straight table:

MonthYear	Count(Emp_code)
	19073
Jun-2013	2
Mar-2014	1
Apr-2014	3
May-2014	3
Jun-2014	1
Jul-2014	10
Aug-2014	2
Sep-2014	17
Oct-2014	34
Nov-2014	35
Dec-2014	178
Jan-2015	130
Feb-2015	157
Mar-2015	205
Apr-2015	240
May-2015	110
Jun-2015	437
Jul-2015	203
Aug-2015	295
Sep-2015	422
Oct-2015	98
Nov-2015	140
Dec-2015	191
Jan-2016	91
Feb-2016	157
Mar-2016	125
Apr-2016	143
May-2016	88
Jun-2016	118
Jul-2016	15437

Your spreadsheet has a lot of 'NULL' entries that were interpreted as today and so that is why according to your rules there is such a high count of Emp_code for Jul-2016.

Regards

Andrew