topic Adding value to existing field in QlikView

Adding value to existing field

Sat, 08 Aug 2015 18:36:17 GMT

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

Re: Adding value to existing field

sunny_talwar — Sat, 08 Aug 2015 18:39:42 GMT

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

Re: Adding value to existing field

maxgro — Sat, 08 Aug 2015 19:06:18 GMT

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;

Re: Adding value to existing field

MarcoWedel — Sat, 08 Aug 2015 23:13:22 GMT

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

Re: Adding value to existing field

MarcoWedel — Sat, 08 Aug 2015 23:22:31 GMT

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