I need new tenure days when title has changed to the employee

[hemachandran]

I have attached the sample data. In that data i have month wise data at the month end.I have tenure days based on the DOJ. When the employee changes from one position to another position the DoJ has to change when changed date of the title. And it has to be the DOJ.

From that date i need new tenure days for the current position. please help on this..

Thanks in Advance..

[sunny_talwar]

May be this:

Table:

LOAD Date#(CalDate, 'DD-MM-YYYY') as CalDate,

     Empcode,

     Month,

     Year,

     Title,

     Date#(DOJ, 'DD-MM-YYYY') as DOJ

FROM

[..\..\..\Downloads\Sample (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

  CalDate - NEW_DOJ as TenureDays;

LOAD *,

  If(Empcode = Previous(Empcode),

  If(Title = Previous(Title), Peek('NEW_DOJ'), CalDate), Alt(DOJ, CalDate)) as NEW_DOJ

Resident Table

Order By Empcode, CalDate;

DROP Table Table;

[vinieme12]

Hi Sunny,

If the OP creates a chart with Empcode & title as dimension and Sum(Tenuredays) this will return incorrect results.

[hemachandran]

Sunny..By using Flag Can u try..When title is changed.Caldate have to be Doj.But when using Flag that particular row oniy changing others rows not changed. Thats the problem i am facing.

Example:

Caldate              empcode            DOJ                   Title        TitleFlag        TempDoj            TENURE

30-01-2016                111                01-01-2016        clerk            0            01-01-2016            30

28-02-2016                111                28-01-2016        manager        1           28-02-2016            0

30-03-2016                111                 28-01-2016       manager        0            01-01-2016           88  answer 30

                                                                                                               answer 28-02-2016

then repeating same DOJ in TempDOJ in that 28-01-2016 have to come..For this i have used order by empcode for creating titleflag..I need in this format sunny..Thanks in advance..Thanks for ur reply..

[vinieme12]

attached ver2

[sunny_talwar]

I don't really know what the finally output needs to be, but yes, you are right if that's what the intention is, then my proposed solution is not going to work.

Thanks,

Sunny

[sunny_talwar]

vinieme12‌ -

Since you pointed out something to me, let me return the favors .

Empcode = 111 seems to changed roles to SeniorSupervisor in Mar. How can he be in that role for 56 days?

[vinieme12]

Hi Sunny,

Please see the excel i posted, 56 is from 30-Mar to 25-May  ... the total duration for which 111 has been recorded as a seniorsuperviosy,

Also Ver2 has been modified for correct results

[sunny_talwar]

Sorry, but I am not sure I understand the reason to do it this way. But again, I might be wrong here and OP probably needs what you have proposed

[sunny_talwar]

First of all, we don't have your script and we are not sure how you are creating flag here. Second, what we have provided thus far isn't giving you what you wanted?? If it isn't then may be we share your script and we might be able to look for issues there.

Best,

Sunny

[hemachandran]

w

when i ur done qvw reload  New_DOJ coming worng Sunny..Thanks for ur reply..