UPR_RELEASED

sagaraperera — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: UPR_RELEASED

rubenmarin — Mon, 17 Jun 2019 07:34:40 GMT

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;

topic Re: UPR_RELEASED in QlikView

UPR_RELEASED

Re: UPR_RELEASED