Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Hi Siva,
Please find attached file for solution, hope it helps you.
Regards,
Jagan.
Hi Siva,
How do u define a salary field in second table?
Pls explain..
Sorry yaar my second table is wrong. 3000 should be replace by 5000 from Aug2011 to dec 2011.
Thank you jagan
But i have hundreds of employee numbers. wat to do?
Thank you vishwa
Directory
;
LOAD
*,
Month(SplitDate) AS Month,
Year(SplitDate) AS Year;
LOAD
*,
AddMonths(FromDate, IterNo() -1) AS SplitDate
WHILE IterNo() < NumSum($(MonthDiff(FromDate, ToDate)), 1);
LOAD
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
];
Instead of this
LOAD * INLINE [
EmployeeNo,FromDate,ToDate,Salary
123,1/1/2011,1/7/2011,3000
123,1/8/2011,1/7/2012,5000
];
u can load ur table whatever it may be(Qvd, Select statement,xml,etc)
Hi,
It works for any number of employees, the logic is same. I used this logic for 10 lakh records.
Regards,
Jagan.
Hello,
Can you please explain the MonthDiff thing