topic Re: add calculated field in QlikView

add calculated field

upaliwije — Thu, 27 Oct 2016 10:35:36 GMT

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

Re: add calculated field

sunny_talwar — Thu, 27 Oct 2016 10:38:45 GMT

Have you tried this:

LOAD class,

[Gross Profit],

[Direct Exp],

[Direct Income],

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

FROM Excel;

Re: add calculated field

upaliwije — Thu, 27 Oct 2016 10:50:07 GMT

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 ?

Re: add calculated field

sunny_talwar — Thu, 27 Oct 2016 11:01:14 GMT

Would you be able to share your script?

Re: add calculated field

upaliwije — Thu, 27 Oct 2016 12:50:23 GMT

Sorry for the delay

CrossTable(CLASS, AMOUNT, 4)

LOAD *

FROM

G:\PRD_PROFIT\*EXP.xls

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

Re: add calculated field

sunny_talwar — Thu, 27 Oct 2016 12:52:39 GMT

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 *

Re: add calculated field

upaliwije — Thu, 27 Oct 2016 13:05:51 GMT

Here is my full script

CrossTable(class, amount, 4)

LOAD BCOD,

Category,

Description,

FROM

G:\PRD_PROFIT\AAEXP.xls

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

Re: add calculated field

upaliwije — Thu, 27 Oct 2016 13:08:33 GMT

I have put following under Description

Gross Profit

Direct Exp

Direct Income

Re: add calculated field

upaliwije — Thu, 27 Oct 2016 14:13:18 GMT

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

CLASS	Description	GROSS PROFIT	Direct Expenses	Expense1	Expense2	Direct Income	NET Profit
A		2,956,865	1,177,685	1,545,008	1,588,282	293,962	-1,060,148
B		413,135	90,591	118,847	122,176	25,631	107,153
C		388,048	362,365	475,387	488,702	76,364	-862,042
D		4,601,248	362,365	475,387	488,702	128,684	3,403,478

http://

Re: add calculated field

sunny_talwar — Thu, 27 Oct 2016 14:21:10 GMT

Like this?

Re: add calculated field

upaliwije — Thu, 27 Oct 2016 14:31:02 GMT

Tks Sunny

I can mange it thuogh I do not understand it fulyy. One more help pls. If I want to a column in between as below shown How should I do it

CLASS	Description	GROSS PROFIT	Direct Expenses	GP after DE	Expense1	Expense2	Direct Income	NET Profit
A		2,956,865	1,177,685	1,779,180	1,545,008	1,588,282	293,962	-1,060,148
B		413,135	90,591	322,544	118,847	122,176	25,631	107,153
C		388,048	362,365	25,683	475,387	488,702	76,364	-862,042
D		4,601,248	362,365	4,238,883	475,387	488,702	128,684	3,403,478

Re: add calculated field

sunny_talwar — Thu, 27 Oct 2016 14:39:10 GMT

Since you wanted to add two additional columns, I changed the approach by using an Island table approach. Also, your sample had two spaces between Direct and Income and that is why the numbers were not matching earlier.

Re: add calculated field

upaliwije — Thu, 27 Oct 2016 14:57:54 GMT

Thanks a lot

But in my document I dont see the result. Pls check and my Attachment and correct me please

Re: add calculated field

sunny_talwar — Thu, 27 Oct 2016 14:59:59 GMT

You did not make the change on the dimension tab and I am guessing you would have not looked on the sort tab also?

Re: add calculated field

sunny_talwar — Thu, 27 Oct 2016 15:02:05 GMT

You have way too many description values for me to fix the sort tab, but here is the dimension fixed

Re: add calculated field

upaliwije — Thu, 27 Oct 2016 15:25:57 GMT

I cant understand pick dim expression. How can I decide how may Dim values should be there . In this case you have put 1,2.,3 . How does it work? pls explain

Re: add calculated field

sunny_talwar — Thu, 27 Oct 2016 15:28:17 GMT

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?

Re: add calculated field

upaliwije — Thu, 27 Oct 2016 15:46:35 GMT

Thanks Sunny,

You helped me so much and I learned a lot. Look forward to your help in future as well

Regards

Re: add calculated field

sunny_talwar — Thu, 27 Oct 2016 15:48:06 GMT

I am glad I was able to offer help. I would request you to mark any helpful response as they might come in handy for others in the future.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny