Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
Contributor III

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

1 Solution

Accepted Solutions
MVP

Maybe something like

RESULT:

FROM YourSource

WHERE Departement <> 'd';

TMP:

NOCONCATENATE

FROM YourSource

WHERE Departement = 'd';

JOIN (TMP)

FROM YourSource;

CONCATENATE (RESULT)

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

RESIDENT TMP;

DROP TABLE TMP;

4 Replies
MVP

Maybe something like

RESULT:

FROM YourSource

WHERE Departement <> 'd';

TMP:

NOCONCATENATE

FROM YourSource

WHERE Departement = 'd';

JOIN (TMP)

FROM YourSource;

CONCATENATE (RESULT)

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

RESIDENT TMP;

DROP TABLE TMP;

Contributor III
Author

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

Journal:

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:

Department, Percentage,

a,45,

b,25,

c,30,

//d,,

] (delimiter is ',');

RESULT:

Resident Journal

WHERE Department <> 'd';

TMP:

NOCONCATENATE

Resident Journal

WHERE Department = 'd';

JOIN (TMP)

Resident Proportions;

CONCATENATE (RESULT)

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

RESIDENT TMP;

Anonymous
Not applicable

Journal:

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:

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;

Contributor III
Author

Thank you verry much Robin for correcting script

Community Browser