11 Replies Latest reply: Dec 21, 2012 2:55 AM by Priya Joshi

# Autogenerate?

Hi all,

I have the table like this,

 EmployeeNo FromDate ToDate Salary 123 1/1/2011 1/7/2011 3000 123 1/8/2011 1/7/2012 5000

Now i want to create table like this,

 EmployeeNo Year Month Salary 123 2011 Jan 3000 123 2011 Feb 3000 123 2011 Mar 3000 123 2011 Apr 3000 123 2011 May 3000 123 2011 Jun 3000 123 2011 Jul 3000 123 2011 Aug 3000 123 2011 Sep 3000 123 2011 Oct 3000 123 2011 Nov 3000 123 2011 Dec 3000 123 2012 Jan 5000 123 2012 Feb 5000 123 2012 Mar 5000 123 2012 Apr 5000 123 2012 May 5000 123 2012 Jun 5000

I have hundreds of employee numbers. Kindly all help me to solve this problem.

Thanks and Regards,

Sivasu

• ###### Re: Autogenerate?

hi  siva

use it,

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07;

this function seperate the string.

or       if you follow these then you can understand easily

index( 'abcdefg', 'cd' ) returns 3

index( 'abcdabcd', 'b', 2 ) returns 6

index( 'abcdabcd', 'b', -2 ) returns 2

left( Date, index( Date,'-') -1 ) where Date = 1997-07-14 returns 1997

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

see the attached image for seperate date(ie seperate string)

• ###### Re: Autogenerate?

Hi Siva,

Please find attached file for solution, hope it helps you.

Regards,

Jagan.

• ###### Re: Autogenerate?

Thank you jagan

But i have hundreds of employee numbers. wat to do?

• ###### Re: Autogenerate?

Hi,

It works for any number of employees, the logic is same.  I used this logic for 10 lakh records.

Regards,

Jagan.

• ###### Re: Autogenerate?

Hello,

Can you please explain the MonthDiff thing

• ###### Re: Autogenerate?

Hi Priya,

Please find my explanation below, hope I clearly explained.

//Function to calculate number of Months Difference between first and second data (\$1) refers first and (\$2) refers second date parameter

SET MonthDiff = Num(((year(\$2) * 12) + month(\$2)) - (((year(\$1) * 12) + month(\$1))) + 1);

Directory;

*,

Month(SplitDate) AS Month,

Year(SplitDate) AS Year;

//Using While here iterating every record to create (ToDate - FromDate) number of months difference,  IterNo() keeps track of Iteration number of each record

*,

AddMonths(FromDate, IterNo() -1) AS SplitDate  //New date field to get incremental date for every month

WHILE IterNo() < NumSum(\$(MonthDiff(FromDate, ToDate)),  1);

//Converting date format in this step

EmployeeNo,

Date(Date#(FromDate, 'D/M/YYYY')) AS FromDate,

Date(Date#(ToDate, 'D/M/YYYY')) AS ToDate,

Salary;

LOAD * INLINE [

EmployeeNo,FromDate,ToDate,Salary

123,1/1/2011,1/7/2011,3000

123,1/8/2011,1/7/2012,5000

];

Regards,

Jagan.

• ###### Re: Autogenerate?

Thank You Very Much

• ###### Re: Autogenerate?

Hi Siva,

How do u define a salary field in second table?

Pls explain..

• ###### Re: Autogenerate?

Sorry yaar my second table is wrong. 3000 should be replace by 5000 from Aug2011 to dec 2011.

• ###### Re: Autogenerate?

Directory

;

*,

Month(SplitDate) AS Month,

Year(SplitDate) AS Year;

*,

AddMonths(FromDate, IterNo() -1) AS SplitDate

WHILE IterNo() < NumSum(\$(MonthDiff(FromDate, ToDate)),  1);

EmployeeNo,

Date(Date#(FromDate, 'D/M/YYYY')) AS FromDate,

Date(Date#(ToDate, 'D/M/YYYY')) AS ToDate,

Salary;

LOAD * INLINE [

EmployeeNo,FromDate,ToDate,Salary

123,1/1/2011,1/7/2011,3000

123,1/8/2011,1/7/2012,5000

]
;