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: 
upaliwije
Creator II
Creator II

add calculated field

Hi,

I have in my excel sheet used for QV documents the following Fields (except new field)

class  Gross Profit   Direct Exp   Direct Income   New Field

A       100                 30              10                    80

B       200              100                50                  150

C        300               150              100                250

I want to create this new field (Gross Profit-Direct Exp+Direct Income) hilighted above either in load script or expression and my output report(pivot table) should look like above.

Pls help to fullfil my requirement

1 Solution

Accepted Solutions
sunny_talwar

So you need 1 for your description and for every new column you need to add, add 1 to Dim. So, if we just had to add NET Profit and GP after DE, I would have had a Dim of 1, 2, 3. If you wanted to add 5 new columns, Dim would be 1, 2, 3,4 ,5, 6

Does this make sense?

View solution in original post

18 Replies
sunny_talwar

Have you tried this:

LOAD class,

          [Gross Profit],

          [Direct Exp],

          [Direct Income],

          [Gross Profit] - [Direct Exp] + [Direct Income] as [New Field]

FROM Excel;

upaliwije
Creator II
Creator II
Author

Tks Sunny

In fact my data is in following format and I am load ing them in cross Table mode

                        A         B         C

Gross Profit      100      200     300

Direct Exp         300    100     150

Direct Income     10      50      100

Is it possible to do what you suggest under cross table load ?

sunny_talwar

Would you be able to share your script?

upaliwije
Creator II
Creator II
Author

Sorry for the delay

CrossTable(CLASS, AMOUNT, 4)

LOAD *

FROM

G:\PRD_PROFIT\*EXP.xls

(biff, embedded labels, table is HO$);

sunny_talwar

You hid all the field names by using *, that is what I needed to see. Can you put down all the fieldnames instead of using a *

upaliwije
Creator II
Creator II
Author

Here is my full script

CrossTable(class, amount, 4)

LOAD BCOD,

     Category,

     Description,

     A,

     B,

     C,

     D

   

FROM

G:\PRD_PROFIT\AAEXP.xls

(biff, embedded labels, table is HO$);

upaliwije
Creator II
Creator II
Author

I have put following under Description

Gross Profit     

Direct Exp       

Direct Income

upaliwije
Creator II
Creator II
Author

I have added my sample QV documents. Pls someone help me add a column (net Profit ) to my pivot table

CLASSDescriptionGROSS PROFITDirect ExpensesExpense1Expense2Direct  IncomeNET Profit
A2,956,8651,177,6851,545,0081,588,282293,962-1,060,148
B413,13590,591118,847122,17625,631107,153
C388,048362,365475,387488,70276,364-862,042
D4,601,248362,365475,387488,702128,6843,403,478

http://

sunny_talwar

Like this?

Capture.PNG