Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I WANT TO DISTRIBUTE THE GROSS AMOUNT IN OVER THE YEAR USING ENTITLEMENT OF THE NO OF DAYS PLEASE FIND THE ATTACHMENT AND HELP ME.
sagara
Hi, this code can do the task. Sample attached.
// Load Excel data using cross table to read each month as a different row (instead of columns)
ExcelData:
CrossTable(Date, Days, 4)
LOAD F1 as REF_NO,
F2 as FROM_DATE,
F3 as TO_DATE,
F4 as GROSS,
*
FROM
[.\SAMPLE.xls]
(biff, embedded labels, header is 1 lines, table is Sheet1$, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'REF_NO'))), // Remove row 4
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'vprior_month'))), // Remove row 3
Remove(Row, RowCnd(Compound, // Remove row 8
RowCnd(CellValue, 1, StrCnd(null)),
RowCnd(CellValue, 5, StrCnd(null))
))
));
// Mapping to retrieve total days
mapTotal:
Mapping LOAD REF_NO, Days
Resident ExcelData where Date='F17'; // 4 dimensions+12 months->next column (17) is for totals
// Table with distributed amount
FinalTable:
LOAD *,
Date#(Date,'DD/MM/YYYY') as DateAsDate, // Original date field is loaded as a text, this field can be used for date operations
GROSS*Days/ApplyMap('mapTotal', REF_NO) as DistributedAmount
Resident ExcelData where IsNum(Date#(Date,'DD/MM/YYYY')); // Keep data from columns with a date in row 2
// Delete first table
DROP Table ExcelData;
Hi, this code can do the task. Sample attached.
// Load Excel data using cross table to read each month as a different row (instead of columns)
ExcelData:
CrossTable(Date, Days, 4)
LOAD F1 as REF_NO,
F2 as FROM_DATE,
F3 as TO_DATE,
F4 as GROSS,
*
FROM
[.\SAMPLE.xls]
(biff, embedded labels, header is 1 lines, table is Sheet1$, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'REF_NO'))), // Remove row 4
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'vprior_month'))), // Remove row 3
Remove(Row, RowCnd(Compound, // Remove row 8
RowCnd(CellValue, 1, StrCnd(null)),
RowCnd(CellValue, 5, StrCnd(null))
))
));
// Mapping to retrieve total days
mapTotal:
Mapping LOAD REF_NO, Days
Resident ExcelData where Date='F17'; // 4 dimensions+12 months->next column (17) is for totals
// Table with distributed amount
FinalTable:
LOAD *,
Date#(Date,'DD/MM/YYYY') as DateAsDate, // Original date field is loaded as a text, this field can be used for date operations
GROSS*Days/ApplyMap('mapTotal', REF_NO) as DistributedAmount
Resident ExcelData where IsNum(Date#(Date,'DD/MM/YYYY')); // Keep data from columns with a date in row 2
// Delete first table
DROP Table ExcelData;