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: 
sagaraperera
Creator
Creator

create a new field

Dear All

I need to create a new field  namely  DESCRIPTIONusing the below given field 

          “BAL_ACCT_CODE AS CACC

BAL_ACCT_CODE AS CACC comprise the appended accounts codes, which should bear the following particulars. In addition by using the codes 10100 minus 15600 we should arrive at the wording “Total_Net_Assest”, ensure that the “Total_Net_Assest” wording should appear in the “DESCRIPTION’ field.

AN ESSENTIAL FIELD “DESCRIPTION’

BAL_ACCT_CODE AS CACC

AMOUNT

FIXED ASSETS

10100

626,043,313

DEPRECIATION

15600

-8,282,434

TOTAL_NET_ASSEST  (10100-15600)

634,325,747 

INVESTMENT EXTERNAL

22035

2,490,625

STATIONERY STOCK

30010

25,900,833

ADVANCES & PRE-PAYMENTS

33000

31,575,750

TRADE DEBTORS

31000

4,122,317,827

RETURN CHEQUES

32000

17,401,462

STAFF LOANS

32600

42,619,432

MY SCRIPT

BS:
LOAD BAL_BRANCH_CODE AS BCOD,
BAL_ACCT_CODE AS CACC,
//BAL_ACCT_YEAR AS TRAN_DATE,
     DATE(Floor(MonthStart(MakeDate(BAL_ACCT_YEAR,BAL_ACCT_PERIOD)))) as TRAN_DATE,
//BAL_ACCT_PERIOD,
     BAL_HC_DBBAL,
BAL_HC_CRBAL
FROM
G:\SGR\QLICK VIEW\QVD\BLSHEET_CU_GL_BALANCE.QVD
(
qvd);

Sagara

8 Replies
Anonymous
Not applicable

what is your expected  output??

sagaraperera
Creator
Creator
Author

i want get a pivot table as follows

DESCRIPTION                      AMOUNT


FIXED ASSETS                                        626,043,313

DEPRECIATION                                          -8,282,434

TOTAL_NET_ASSEST                               634,325,747

INVESTMENT EXTERNAL                             2,490,625



SAGARA


Anonymous
Not applicable

do you have any mapping between  BAL_ACCT_CODE   and  description field

devarasu07
Master II
Master II

Hi,

You can try like this

Temp:

LOAD * INLINE [

    DESCRIPTION, CACC, AMOUNT

    FIXED ASSETS, 10100, 626043313

    DEPRECIATION, 15600, -8282434

    INVESTMENT EXTERNAL, 22035, 2490625

    STATIONERY STOCK, 30010, 25900833

    ADVANCES & PRE-PAYMENTS, 33000, 31575750

    TRADE DEBTORS, 31000, 4122317827

    RETURN CHEQUES, 32000, 17401462

    STAFF LOANS, 32600, 42619432

];

Dim:

load * Inline [

Dim

1

2

];

 

Chart dim:

=if(Dim=1,DESCRIPTION,'TOTAL_NET_ASSEST')

exp:

if(Dim=1,sum(AMOUNT), sum({<CACC={'10100'}>}AMOUNT)-sum({<CACC={'15600'}>}AMOUNT))

custom sort exp:

match(if(Dim=1,DESCRIPTION,'TOTAL_NET_ASSEST'),'FIXED ASSETS','DEPRECIATION','TOTAL_NET_ASSEST','ADVANCES & PRE-PAYMENTS','INVESTMENT EXTERNAL','RETURN CHEQUES','STAFF LOANS','STATIONERY STOCK','TRADE DEBTORS')

Capture.JPG

sagaraperera
Creator
Creator
Author

Dear Devarasu

thanks for your reply, can i help small favor,


what place insert given below


Chart dim:

=if(Dim=1,DESCRIPTION,'TOTAL_NET_ASSEST')


sagara

devarasu07
Master II
Master II

In your pivot  chart  create calculated dimension using above expression which i mentioned. Thanks

devarasu07
Master II
Master II

Hi,

PFA, Same output using qliksense. (PS: update your custom sorting based on your requirement.)

Capture.JPG

If your issue got resolved try to close this thread by  marking as correct. tks

Thanks

Deva

Mark_Little
Luminary
Luminary

HI,

I would advised having a look at this

How IntervalMatch Solved My Profit and Loss Dilemma

I have used this approach to make both Balance sheet and Profit and loss reports

Mark