topic Re: distribute value in load in QlikView

distribute value in load

bilionut — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

Start load from table with two dimensions: Department and Account

Department	Account	Value
a	x	2
a	x	4
a	z	2
b	x	3
b	y	4
b	z	5
c	x	3
c	x	2
c	y	4
d	x	4
d	y	3
d	z	5
d	y	5

I have to distribute all values for the Department "d" to all other Departments corresponding to each Account , based on the proportions described in next table :

Department	%
a	45
b	15
c	40

The number of possible department values is not limited. I limited to "a,b, c, d" just for the simplicity of example

The result of distribution should look like this ( of course, except column Formula, which is added only as an explanation):

Department	Account		(Formula)			Value
a	x					2
a	x					4
...................	...............					...........
ad	x	4	x	45	=	1.8
ad	y	3	x	45	=	1.35
ad	y	5	x	45	=	2.25
ad	z	5	x	45	=	2.25
bd	x	4	x	15	=	0.6
bd	y	3	x	15	=	0.45
bd	y	5	x	15	=	0.75
bd	z	5	x	15	=	0.75
cd	x	4	x	40	=	1.6
cd	y	3	x	40	=	1.2
cd	y	5	x	40	=	2
cd	z	5	x	40	=	2

I would prefer to make this in load section , but it would be good a solution in a chart object

Thanks in advance,

Re: distribute value in load

swuehl — Sat, 11 Mar 2017 09:14:54 GMT

Guess you are loading a table with the percentage distribution?

Maybe something like

RESULT:

LOAD Department, Account, Value

FROM YourSource

WHERE Departement <> 'd';

TMP:

NOCONCATENATE

LOAD Department, Account, Value

FROM YourSource

WHERE Departement = 'd';

JOIN (TMP)

LOAD Department as DistDepartment, Percentage

FROM YourSource;

CONCATENATE (RESULT)

LOAD DistDepartment & Department as Department, Account, Value * Percentage as Value

RESIDENT TMP;

DROP TABLE TMP;

Re: distribute value in load

bilionut — Sat, 11 Mar 2017 09:53:27 GMT

Thank you for your quick answer

I tried to adapt your solution in the example below, but something goes wrong in final

Journal:

LOAD * inline [

Department, Account, Value,

a,2,x

a,3,w

a,4,x

a,2,z

b,3,x

b,4,y

b,5,z

c,2,w

c,3,x

c,4,y

c,5,w

c,2,x

d,3,y

d,4,x

d,5,z

d,2,w

d,23,y

d,5,x

d,7,z

d,1,w

] (delimiter is ',');

Proportions:

LOAD * inline [

Department, Percentage,

a,45,

b,25,

c,30,

//d,,

] (delimiter is ',');

RESULT:

LOAD Department, Account, Value

Resident Journal

WHERE Department <> 'd';

TMP:

NOCONCATENATE

LOAD Department, Account, Value

Resident Journal

WHERE Department = 'd';

JOIN (TMP)

LOAD Department as DistDepartment, Percentage

Resident Proportions;

CONCATENATE (RESULT)

LOAD DistDepartment & Department as Department, Account, Value * Percentage as Value

RESIDENT TMP;

Re: distribute value in load

Anonymous — Sat, 11 Mar 2017 10:17:34 GMT

Journal:

LOAD * inline [

Department, Value, Account
a,2,x
a,3,w
a,4,x
a,2,z
b,3,x
b,4,y
b,5,z
c,2,w
c,3,x
c,4,y
c,5,w
c,2,x
d,3,y
d,4,x
d,5,z
d,2,w
d,23,y
d,5,x
d,7,z
d,1,w

] (delimiter is ',');

Proportions:

LOAD * inline [
Department, Percentage,
a,45,
b,25,
c,30
] (delimiter is ',');

NoConcatenate

RESULT:

LOAD Department, Account, Value Resident Journal WHERE Department <> 'd';

TMP:

NOCONCATENATE

LOAD Department, Account, Value Resident Journal WHERE Department = 'd';

JOIN (TMP)

LOAD Department as DistDepartment, Percentage Resident Proportions;

CONCATENATE (RESULT)

LOAD DistDepartment & Department as Department, Account, Value * Percentage as Value

RESIDENT TMP;

Re: distribute value in load

bilionut — Sat, 11 Mar 2017 12:15:59 GMT

Thank you verry much Robin for correcting script