Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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);
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