topic Re: Load Script Summation of certain rows based on row value in QlikView

Load Script Summation of certain rows based on row value

RogerG — Mon, 01 Feb 2021 14:29:29 GMT

Hello,

I have a table like this

Table:

load * inline

Project, id, type, cost

P1, id1, external, 1000

P1, id2, external, 2000

P1, id3, supplies, 3000

p1, id4, personnel, 4000

];

I need to create a new type called Internal

this field would be a summation of external and supplies only.

I have the master table created as

load * inline

Project, type, cost

P1, external, 3000

P1, supplies, 3000

p1, personnel, 4000

P1, Internal, 0 // I am not sure how to adjust this to add the 2 together

];

Re: Load Script Summation of certain rows based on row value

edwin — Mon, 01 Feb 2021 15:40:41 GMT

it sounds like what you may want is create a new field that categorizes the types:

Table:
load *, if(type='external','external','internal') as category inline [
Project, id, type, cost
P1, id1, external, 1000
P1, id2, external, 2000
P1, id3, supplies, 3000
p1, id4, personnel, 4000
];

hope that helps

Re: Load Script Summation of certain rows based on row value

edwin — Mon, 01 Feb 2021 15:41:26 GMT

this way when you filter for category=internal, it will return the types that are not external

Re: Load Script Summation of certain rows based on row value

RogerG — Mon, 01 Feb 2021 18:55:55 GMT

so the equations I am generating are "Types" in this case, I can use a separate field for summation,

The equation will be something like Externals = Externals + Supplies, (no problem here)

then

Internals = .5*(Externals - Supplies)

so the types will read

Externals

Personnel

Supplies

Internals

with their associated dollar values