UPR_RELEASED

[sagaraperera]

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

[rubenmarin · Accepted solution]

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;