Applying an Expression to load inline item & Get the results in a Pivot table

[MMARG83]

Dear All..

I have two field items which i added via load inline (STAFF SALARY and CASUAL WAGE), since they were not included as the field values in the dimension named DESCRIPTION.

 LOAD DESCRIPTION,
     [DR AMT],
     [CR AMT],
     BRCOE,
     [Print Time :],
     0.46020833333022892,
     [JURNAL VOUCHER LISTING],
     [August - 2023],
     BIYAGAMA,
     F10,
     ACCOUNT,
     [DEBIT (Rs.)],
     [CREDIT (Rs.)]
FROM
[F:\QV WORK\SALARY JE .xlsx]
(ooxml, embedded labels, table is SOURCE);


load *inline
[
DESCRIPTION,GL CODE
'STAFF SALARY','76110'
'CASUAL WAGE', '76130'
];

And I need to add Expression to these two items (STAFF SALARY and CASUAL WAGE) based on two field values included in the dimension (EPF 12 % , GRATIUTY A/C).

 Calculation as follows.

• STAFF SALARY  = (GRATIUTY A/C )/12*100                                 = (90,000)/12*100 = 750,000
• CASUAL WAGE = ((EPF 12 % ) – (GRATIUTY A/C))/12*100    = (117,500 – 90,000)/12* 100 = 229,167

My expected output looks like below

DESCRIPTION	GL CODE	GL NAME	NARRATION	DR	CR
BASIC SALARY	76110	STAFF SALARY	BASIC SALARY	750000	0
BASIC SALARY	41000	SALARY CONTROL	BASIC SALARY		750000
BASIC SALARY	76130	CASUAL WAGE	BASIC SALARY	229167	0
BASIC SALARY	41000	SALARY CONTROL	BASIC SALARY		229167

Work done up to now by me……………………………………………………………………………………………………………………..

DESCRIPTION	GL CODE	GL NAME	NARRATION	DR	CR
EPF 8 %	41000	SALARY CONTROL	DEDUCTIONS	14088	0
EPF 8 %	41200	EPF PAYABLE	DEDUCTIONS	0	14088
Stamp Duty	41000	SALARY CONTROL	DEDUCTIONS	100	0
Stamp Duty	44200	STAMP DUTY	DEDUCTIONS	0	100
MEDICAL FUND MEM.CON	41000	SALARY CONTROL	DEDUCTIONS	1250	0
MEDICAL FUND MEM.CON	41800	MEDICAL FUND	DEDUCTIONS	0	1250
PENSION FUND 01	41000	SALARY CONTROL	DEDUCTIONS	2500	0
PENSION FUND 01	41420	PENSION FUND	DEDUCTIONS	0	2500
SPORTS CLUB	41000	SALARY CONTROL	DEDUCTIONS	125	0
SPORTS CLUB	43200	SPORTS CLUB	DEDUCTIONS	0	125
UNION FEES	41000	SALARY CONTROL	DEDUCTIONS	625	0
UNION FEES	42800	UNION FEES	DEDUCTIONS	0	625
FESTIVAL LOAN - ( CAPITAL )	32800	FESTIVAL LOANPAYABLE	DEDUCTIONS	0	5258.33
FESTIVAL LOAN - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	5258.33	0
S.S INTERNATIONAL PREMIUM - ( CAPITAL )	32500	S.S. PAYABLE	DEDUCTIONS	0	5268.67
S.S INTERNATIONAL PREMIUM - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	5268.67	0
S.S PREMIUM LOAN - ( CAPITAL )	32500	S.S. PAYABLE	DEDUCTIONS	0	3326.1
S.S PREMIUM LOAN - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	3326.1	0
SUNDRY LOAN 5 - ( CAPITAL )	32600	SUNDRY LOAN PAYABLE	DEDUCTIONS	0	4841.67
SUNDRY LOAN 5 - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	4841.67	0
EPF 12 %	41200	EPF PAYABLE	STATUTORY PAY	0	21132
EPF 12 %	76150	EPF EXPENCE	STATUTORY PAY	21132	0
ETF 3%	41400	EPF PAYABLE	STATUTORY PAY	0	5283
ETF 3%	76170	ETF EXPENCE	STATUTORY PAY	5283	0
GRATIUTY A/C	42000	GRATUITY PAYABLE	STATUTORY PAY	0	21132
GRATIUTY A/C	76190	GRATIUTY EXPENCE	STATUTORY PAY	21132	0
MEDICAL FUND A/C	37540	MEDICAL FUND	STATUTORY PAY	0	3750
MEDICAL FUND A/C	76230	MEDICAL FUND	STATUTORY PAY	3750	0
EPF 8 %	41000	SALARY CONTROL	DEDUCTIONS	78333.36	0
EPF 8 %	41200	EPF PAYABLE	DEDUCTIONS	0	78333.36
Stamp Duty	41000	SALARY CONTROL	DEDUCTIONS	400	0
Stamp Duty	44200	STAMP DUTY	DEDUCTIONS	0	400
MEDICAL FUND MEM.CON	41000	SALARY CONTROL	DEDUCTIONS	3000	0
MEDICAL FUND MEM.CON	41800	MEDICAL FUND	DEDUCTIONS	0	3000
PENSION FUND 01	41000	SALARY CONTROL	DEDUCTIONS	4468	0
PENSION FUND 01	41420	PENSION FUND	DEDUCTIONS	0	4468
SPORTS CLUB	41000	SALARY CONTROL	DEDUCTIONS	300	0
SPORTS CLUB	43200	SPORTS CLUB	DEDUCTIONS	0	300
UNION FEES	41000	SALARY CONTROL	DEDUCTIONS	2000	0
UNION FEES	42800	UNION FEES	DEDUCTIONS	0	2000
FESTIVAL LOAN - ( CAPITAL )	32800	FESTIVAL LOANPAYABLE	DEDUCTIONS	0	16895.83
FESTIVAL LOAN - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	16895.83	0
S.S INTERNATIONAL PREMIUM - ( CAPITAL )	32500	S.S. PAYABLE	DEDUCTIONS	0	31612.02
S.S INTERNATIONAL PREMIUM - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	31612.02	0
S.S PREMIUM LOAN - ( CAPITAL )	32500	S.S. PAYABLE	DEDUCTIONS	0	14826.1
S.S PREMIUM LOAN - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	14826.1	0
SUNDRY LOAN 5 - ( CAPITAL )	32600	SUNDRY LOAN PAYABLE	DEDUCTIONS	0	40793.05
SUNDRY LOAN 5 - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	40793.05	0
EPF 12 %	41200	EPF PAYABLE	STATUTORY PAY	0	117500
EPF 12 %	76150	EPF EXPENCE	STATUTORY PAY	117500	0
ETF 3%	41400	EPF PAYABLE	STATUTORY PAY	0	29375.01
ETF 3%	76170	ETF EXPENCE	STATUTORY PAY	29375.01	0
GRATIUTY A/C	42000	GRATUITY PAYABLE	STATUTORY PAY	0	90000
GRATIUTY A/C	76190	GRATIUTY EXPENCE	STATUTORY PAY	90000	0
MEDICAL FUND A/C	37540	MEDICAL FUND	STATUTORY PAY	0	9000
MEDICAL FUND A/C	76230	MEDICAL FUND	STATUTORY PAY	9000	0
PAYE TAX	41000	SALARY CONTROL	DEDUCTIONS	5213	0
PAYE TAX	41100	PAYE PAYABLE	DEDUCTIONS	0	5213
CERESA EMP COOP BANK	41000	SALARY CONTROL	DEDUCTIONS	1500	0
CERESA EMP COOP BANK	44000	ACCRUDE CHARGE	DEDUCTIONS	0	1500
SALARY ADVANCE	32810	SALARY ADVANCE	DEDUCTIONS	0	67500
SALARY ADVANCE	41000	SALARY CONTROL	DEDUCTIONS	67500	0
EPF 8 %	41000	SALARY CONTROL	DEDUCTIONS	142284	0
EPF 8 %	41200	EPF PAYABLE	DEDUCTIONS	0	142284
Stamp Duty	41000	SALARY CONTROL	DEDUCTIONS	825	0
Stamp Duty	44200	STAMP DUTY	DEDUCTIONS	0	825
MEDICAL FUND MEM.CON	41000	SALARY CONTROL	DEDUCTIONS	8500	0
MEDICAL FUND MEM.CON	41800	MEDICAL FUND	DEDUCTIONS	0	8500
PENSION FUND 01	41000	SALARY CONTROL	DEDUCTIONS	8000	0
PENSION FUND 01	41420	PENSION FUND	DEDUCTIONS	0	8000
SPORTS CLUB	41000	SALARY CONTROL	DEDUCTIONS	850	0
SPORTS CLUB	43200	SPORTS CLUB	DEDUCTIONS	0	850
UNION FEES	41000	SALARY CONTROL	DEDUCTIONS	5650	0
UNION FEES	42800	UNION FEES	DEDUCTIONS	0	5650
FESTIVAL LOAN - ( CAPITAL )	32800	FESTIVAL LOANPAYABLE	DEDUCTIONS	0	28145.84
FESTIVAL LOAN - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	28145.84	0
S.S INTERNATIONAL PREMIUM - ( CAPITAL )	32500	S.S. PAYABLE	DEDUCTIONS	0	42149.44
S.S INTERNATIONAL PREMIUM - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	42149.44	0
S.S PREMIUM LOAN - ( CAPITAL )	32500	S.S. PAYABLE	DEDUCTIONS	0	23745.8
S.S PREMIUM LOAN - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	23745.8	0
SUNDRY LOAN 5 - ( CAPITAL )	32600	SUNDRY LOAN PAYABLE	DEDUCTIONS	0	46996.25
SUNDRY LOAN 5 - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	46996.25	0
EPF 12 %	41200	EPF PAYABLE	STATUTORY PAY	0	213426
EPF 12 %	76150	EPF EXPENCE	STATUTORY PAY	213426	0
ETF 3%	41400	EPF PAYABLE	STATUTORY PAY	0	53356.5
ETF 3%	76170	ETF EXPENCE	STATUTORY PAY	53356.5	0
GRATIUTY A/C	42000	GRATUITY PAYABLE	STATUTORY PAY	0	159086
GRATIUTY A/C	76190	GRATIUTY EXPENCE	STATUTORY PAY	159086	0
MEDICAL FUND A/C	37540	MEDICAL FUND	STATUTORY PAY	0	25500
MEDICAL FUND A/C	76230	MEDICAL FUND	STATUTORY PAY	25500	0
PAYE TAX	41000	SALARY CONTROL	DEDUCTIONS	321	0
PAYE TAX	41100	PAYE PAYABLE	DEDUCTIONS	0	321
CERESA EMP COOP BANK	41000	SALARY CONTROL	DEDUCTIONS	3000	0
CERESA EMP COOP BANK	44000	ACCRUDE CHARGE	DEDUCTIONS	0	3000
SALARY ADVANCE	32810	SALARY ADVANCE	DEDUCTIONS	0	110400
SALARY ADVANCE	41000	SALARY CONTROL	DEDUCTIONS	110400	0

Expressions written up to now

Expression for “DR”

SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'PAYE TAX'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'EPF 8 %'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'Stamp Duty'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'CERESA EMP COOP BANK'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'MEDICAL FUND MEM.CON'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'PENSION FUND 01'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'SALARY ADVANCE'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'SPORTS CLUB'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'UNION FEES'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'FESTIVAL LOAN - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'S.S INTERNATIONAL PREMIUM - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'S.S PREMIUM LOAN - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'SUNDRY LOAN 5 - ( CAPITAL )'}>}[CR AMT])
++
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'EPF 12 %'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'ETF 3%'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'GRATIUTY A/C'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'MEDICAL FUND A/C'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'PENSION-01 FUND COM. CONT.'}>}[DR AMT])

Expression for “CR”

SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'PAYE TAX'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'EPF 8 %'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'Stamp Duty'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'CERESA EMP COOP BANK'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'MEDICAL FUND MEM.CON'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'PENSION FUND 01'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'SALARY ADVANCE'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'SPORTS CLUB'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'UNION FEES'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'FESTIVAL LOAN - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'S.S INTERNATIONAL PREMIUM - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'S.S PREMIUM LOAN - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'SUNDRY LOAN 5 - ( CAPITAL )'}>}[CR AMT])
++
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'EPF 12 %'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'ETF 3%'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'GRATIUTY A/C'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'MEDICAL FUND A/C'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'PENSION-01 FUND COM. CONT.'}>}[DR AMT])

Please can anyone help me….

Hope all the details given….

Thank you.

MMARG83