Applying an expression to field values which were entered to Source throuth Load * Inline.

[MMARG83]

Dear All.

I'm trying to create a Journal Entry via Qlik view using an Excel document exported by the salary system. Journal Entry consist of Three parts,

1. BASIC SALARY
2. DEDUCTIONS
3. STATUTORY PAY

I was able to create the part "2" and "3" by myself and need your kind help to create part "1" that is the BASIC SALARY part. Basic salary is made up of Two parts that is,

1. STAFF SALARY - salary paid to permanent employees
2. CASUAL WAGE - salary paid to contract employees.

Our salary system doesn't have these above mentioned STAFF SALARY & CASUAL WAGES items, therefor above two values doesn't included in dimension as field values so that I  had to insert those two values  using Load * Inline.  

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

We  have to calculate STAFF SALARY & CASUAL WAGES amounts  based on  "GRATIUTY A/C" amount and "EPF 12 %" amounts.("GRATIUTY A/C" amount and "EPF 12 %" amounts are field values included in the dimension )

• GRATIUTY A/C - Only paid to Permanent employees @ 12% on their Basic Salaries.
• EPF 12 %           - Paid to all Permanent & Contract employees @ 12% on their Basic Salaries.

Calculation for the STAFF SALARY & CASUAL WAGES as follows.

• STAFF SALARY    - ( "GRATIUTY A/C")/12*100
• CASUAL WAGES - ( ( "EPF 12 %") - ( "GRATIUTY A/C") )/12*100

Expected output looks like this.

BRCOE	DESCRIPTION	GL CODE	GL NAME	NARRATION	DR	CR
GM00	BASIC SALARY	76110	STAFF SALARY	BASIC SALARY	750000
GM00	BASIC SALARY	41000	SALARY CONTROL	BASIC SALARY		750000
GM00	BASIC SALARY	76130	CASUAL WAGE	BASIC SALARY	229167
GM00	BASIC SALARY	41000	SALARY CONTROL	BASIC SALARY		229167
GM00	EPF 8 %	41000	SALARY CONTROL	DEDUCTIONS	78333.36	0
GM00	EPF 8 %	41200	EPF PAYABLE	DEDUCTIONS	0	78333.36
GM00	Stamp Duty	41000	SALARY CONTROL	DEDUCTIONS	400	0
GM00	Stamp Duty	44200	STAMP DUTY	DEDUCTIONS	0	400
GM00	MEDICAL FUND MEM.CON	41000	SALARY CONTROL	DEDUCTIONS	3000	0
GM00	MEDICAL FUND MEM.CON	41800	MEDICAL FUND	DEDUCTIONS	0	3000
GM00	PENSION FUND 01	41000	SALARY CONTROL	DEDUCTIONS	4468	0
GM00	PENSION FUND 01	41420	PENSION FUND	DEDUCTIONS	0	4468
GM00	SPORTS CLUB	41000	SALARY CONTROL	DEDUCTIONS	300	0
GM00	SPORTS CLUB	43200	SPORTS CLUB	DEDUCTIONS	0	300
GM00	UNION FEES	41000	SALARY CONTROL	DEDUCTIONS	2000	0
GM00	UNION FEES	42800	UNION FEES	DEDUCTIONS	0	2000
GM00	FESTIVAL LOAN - ( CAPITAL )	32800	FESTIVAL LOAN PAYABLE	DEDUCTIONS	0	16895.83
GM00	FESTIVAL LOAN - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	16895.83	0
GM00	S.S INTERNATIONAL PREMIUM - ( CAPITAL )	32500	S.S. PAYABLE	DEDUCTIONS	0	31612.02
GM00	S.S INTERNATIONAL PREMIUM - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	31612.02	0
GM00	S.S PREMIUM LOAN - ( CAPITAL )	32500	S.S. PAYABLE	DEDUCTIONS	0	14826.1
GM00	S.S PREMIUM LOAN - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	14826.1	0
GM00	SUNDRY LOAN 5 - ( CAPITAL )	32600	SUNDRY LOAN PAYABLE	DEDUCTIONS	0	40793.05
GM00	SUNDRY LOAN 5 - ( CAPITAL )	41000	SALARY CONTROL	DEDUCTIONS	40793.05	0
GM00	PAYE TAX	41000	SALARY CONTROL	DEDUCTIONS	5213	0
GM00	PAYE TAX	41100	PAYE PAYABLE	DEDUCTIONS	0	5213
GM00	CERESA EMP COOP BANK	41000	SALARY CONTROL	DEDUCTIONS	1500	0
GM00	CERESA EMP COOP BANK	44000	ACCRUDE CHARGE	DEDUCTIONS	0	1500
GM00	SALARY ADVANCE	32810	SALARY ADVANCE	DEDUCTIONS	0	67500
GM00	SALARY ADVANCE	41000	SALARY CONTROL	DEDUCTIONS	67500	0
GM00	EPF 12 %	41200	EPF PAYABLE	STATUTORY PAY	0	117500.04
GM00	EPF 12 %	76150	EPF EXPENCE	STATUTORY PAY	117500.04	0
GM00	ETF 3%	41400	EPF PAYABLE	STATUTORY PAY	0	29375.01
GM00	ETF 3%	76170	ETF EXPENCE	STATUTORY PAY	29375.01	0
GM00	GRATIUTY A/C	42000	GRATUITY PAYABLE	STATUTORY PAY	0	90000
GM00	GRATIUTY A/C	76190	GRATIUTY EXPENCE	STATUTORY PAY	90000	0
GM00	MEDICAL FUND A/C	37540	MEDICAL FUND	STATUTORY PAY	0	9000
GM00	MEDICAL FUND A/C	76230	MEDICAL FUND	STATUTORY PAY	9000	0

Expression for "DR" is,

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" is,

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])

SCRIPT USED IS  LIKE THIS.

SALARY:
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 [GL CODE],
[GL NAME],
DESCRIPTION,
[GL CODE TYPE],
NARRATION,
CAT
FROM
[F:\QV WORK\SALARY JE .xlsx]
(ooxml, embedded labels, table is CODE);

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

Hope that all the details provided.

Please help me to create the BASIC SALARY part of the Journal Entry.

Thank you.

.MMARG83.