Hi Everybody I have a question, this is my scenario:
In my chart table I need to fill rows with information missing
Original
Moth Salary ENE FEB MAR ABR $22,000.00 MAY JUN JUL AGO SEP OCT NOV $24,200.00 DIC
Target
Moth Salary ENE $22,000.00 FEB $22,000.00 MAR $22,000.00 ABR $22,000.00 MAY $22,000.00 JUN $22,000.00 JUL $22,000.00 AGO $22,000.00 SEP $22,000.00 OCT $22,000.00 NOV $24,200.00 DIC $24,200.00
As you can see the information missing should be filled with previous information
Look at the functions Above(), Below(), Before(), and After(), perhaps you can use them.
However, how do you know what the salary was in Jan, Feb, and March - it could have been 20,000 ? You don't have any previous value to base your decision on...
Assuming this is for the load script, and you want to back fill the first few months with the first known value, you could use something like:
Input:
LOAD *, RowNo() as MonthNum INLINE
[
Month, Salary
ENE
FEB
MAR
ABR $22,000.00
MAY
JUN
JUL
AGO
SEP
OCT
NOV $24,200.00
DIC
];
// Fill down
Temp:
NoConcatenate
LOAD Month,
If(Len(Salary) = 0, Peek(Salary), Salary) as Salary,
MonthNum
Resident Input
Order by MonthNum;
// Back fill up
Result:
NoConcatenate
LOAD Month,
If(Len(Salary) = 0, Peek(Salary), Salary) as Salary,
MonthNum
Resident Input
Order by MonthNum DESC;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Look at the functions Above(), Below(), Before(), and After(), perhaps you can use them.
However, how do you know what the salary was in Jan, Feb, and March - it could have been 20,000 ? You don't have any previous value to base your decision on...
Assuming this is for the load script, and you want to back fill the first few months with the first known value, you could use something like:
Input:
LOAD *, RowNo() as MonthNum INLINE
[
Month, Salary
ENE
FEB
MAR
ABR $22,000.00
MAY
JUN
JUL
AGO
SEP
OCT
NOV $24,200.00
DIC
];
// Fill down
Temp:
NoConcatenate
LOAD Month,
If(Len(Salary) = 0, Peek(Salary), Salary) as Salary,
MonthNum
Resident Input
Order by MonthNum;
// Back fill up
Result:
NoConcatenate
LOAD Month,
If(Len(Salary) = 0, Peek(Salary), Salary) as Salary,
MonthNum
Resident Input
Order by MonthNum DESC;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
This solution worked but now I have changed the requirement with the following rules:
There is a table with this record.
1. Consider the date Start_date as labor start or start of month
2. If there are no changes in the salary since we started work to date / Current month with the same salary
3. If the employee has a salary change in the same month, a record must be generated with the same results with the same month
4. If the employee changes roles but retains the same salary as a new record
5. If the employee was discharged and returned to the company should generate only the month / year record in working life, then start again to fill
the table with the records since its new start
