topic sum based on selection to new value. Loadscript in QlikView

sum based on selection to new value. Loadscript

Mon, 19 May 2014 15:56:46 GMT

In the loadscript I load the following columns from table A.

Productgroup

Productgroup name

Itemgroup

Itemgroup name

Item SKU

Item Name

Sales amount

The output from that is

Productgroup	Productgroup name	Itemgroup	Itemgroup name	Item SKU	Item Name	Sales amount
10	Bindemedel o bruk	1001	Cement	002805618	CEMENT STD PORTL 25KG	1022,37
10	Bindemedel o bruk	1001	Cement	002843575	BYGGCEMENT 40X25KG	412,84
10	Bindemedel o bruk	1002	Bruksbindemedel	003491969	NATURGIPS GYPSUM 40X25KG	14968,77
10	Bindemedel o bruk	1005	Torrbruk	001339536	GROVBETONG C30/37 25KG	648,23
10	Bindemedel o bruk	1005	Torrbruk	001339537	FINBETONG C30/37 25KG	-95,53
10	Bindemedel o bruk	1005	Torrbruk	001339541	MURBRUK B GULLEX M 2,5 40X25KG	103,62
10	Bindemedel o bruk	1005	Torrbruk	002416539	PUTSBRUK C BASE 135 HAND 25KG	-2,55
10	Bindemedel o bruk	1005	Torrbruk	002931354	EXPANDERBETONG EXM702 FIN 25KG	1124,61
10	Bindemedel o bruk	1008	Avjämningsmassa	002416948	SNABBSLIPSATS FLOOR318GROV 25	-35,31
10	Bindemedel o bruk	1008	Avjämningsmassa	002416949	SNABBSLIPSATS FLOO319 FIN 25KG	-32,76

From this I want to (in the loadscript if possible)

sum the "sales amount" based on selection in "itemgroup"and give a new value.

Exampel:

Sum salesamount where Itemgroup ID = 1001 and 1002 to "indoor"

Sum salesamount where Itemgroup ID = 1005,1008 and 1008 to "outdoor"

Re: sum based on selection to new value. Loadscript

preminqlik — Mon, 19 May 2014 16:09:16 GMT

Load *,

if(match(Itemgroup,1001,1002),'INDOOR',if(match(Itemgroup,1005,1008),'OUTDOOR')) as NEWGROUP

from path;

Re: sum based on selection to new value. Loadscript

Anonymous — Mon, 19 May 2014 16:13:33 GMT

Hi Johan,

Is this what you are looking for?

Cheers,

Antoine

Re: sum based on selection to new value. Loadscript

maxgro — Mon, 19 May 2014 16:23:42 GMT

just add a field as Prem Kumar suggested

and make the sum and all others calc in charts


LOAD Productgroup, 
     [Productgroup name], 
     Itemgroup, 
     [Itemgroup name], 
     [Item SKU], 
     [Item Name], 
     [Sales amount],
     pick(wildmatch(Itemgroup,1001,1002,1005,'*'), 'indoor', 'indoor', 'outdoor', '???') as NewField
FROM
[http://community.qlik.com/thread/118961]
(html, codepage is 1252, embedded labels, table is @2);