Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sagaraperera
New Contributor III

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
sahadevpatil
New Contributor III

Re: create a new field

what is your expected  output??

sagaraperera
New Contributor III

Re: create a new field

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


kulwantsaj219
Contributor II

Re: create a new field

do you have any mapping between  BAL_ACCT_CODE   and  description field

devarasu07
Honored Contributor II

Re: create a new field

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
New Contributor III

Re: create a new field

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
Honored Contributor II

Re: create a new field

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

devarasu07
Honored Contributor II

Re: create a new field

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

mark6505
Valued Contributor III

Re: create a new field

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

Community Browser