Adding value to existing field

[#1]

Hi All,

I have 7 values in Category column like Gross,Contractual,Currency,SpecialDisc,Returns,Price,Other.

My req is i have to add one more value NetRevenue (NetRevenue=GrossAmount-Contractual-SpecialDisc-Returns-Price-Other+Currency) into that column. So finally we will have 8 field values.

I tried in script level in category column it's showing 8 values but if i add this column into table and did calculation as sum(amount).

But in the table it's showing 7 values.

Can anybody help me here? I am attaching QVW also here.

Thanks

[sunny_talwar]

Working with Personal Edition of QlikView, would you be able to post your script here and screenshot of the front end table???

[maxgro]

if you want to add NetRevenue in the script

Tab1:

LOAD * Inline [

Category,Amount

Gross,10000

Contractual,1400

Currency,2000

SpecialDisc,1000

Returns,1000

Price,1400

Other,1200

];

load

  'NetRevenue' as Category,

  Amount *

  pick(wildmatch(Category, 'Gross', 'Contractual', 'SpecialDisc', 'Returns', 'Price', 'Other', 'Currency', '*'),

  1, -1, -1, -1, -1, -1, 1, 0) as Amount

Resident Tab1;

[MarcoWedel]

Hi,

one solution could be:

Tab1:
LOAD * Inline [
Category,Amount
Gross,10000
Contractual,1400
Currency,2000
SpecialDisc,1000
Returns,1000
Price,1400
Other,1200
];

LOAD 'NetRevenue' as Category,
    Sum(-((Category='Gross')-(Category='Contractual')-(Category='SpecialDisc')-(Category='Returns')-(Category='Price')-(Category='Other')+(Category='Currency'))*Amount) as Amount
Resident Tab1;

hope this helps

regards

Marco

[MarcoWedel · Accepted solution]

Although I think Massimo's solution is more elegant than mine.

Here with an added aggregation to only get one table row in Tab1 for NetRevenue:

Tab1:
LOAD * Inline [
Category,Amount
Gross,10000
Contractual,1400
Currency,2000
SpecialDisc,1000
Returns,1000
Price,1400
Other,1200
];

LOAD
  'NetRevenue' as Category,
  Sum(Amount*Pick(WildMatch(Category, 'Gross', 'Contractual', 'SpecialDisc', 'Returns', 'Price', 'Other', 'Currency', '*'),
                  1, -1, -1, -1, -1, -1, 1, 0)) as Amount
Resident Tab1;

regards

Marco