Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
MMARG83
Contributor II
Contributor II

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

 

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

MMARG83_0-1696457662462.png

 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

Labels (4)
0 Replies