Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bilionut
Contributor III
Contributor III

distribute value in load

Hi there,

Start load from table with two dimensions: Department and Account

 

DepartmentAccountValue
ax2
ax4
az2
bx3
by4
bz5
cx3
cx2
cy4
dx4
dy3
dz5
dy5

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   %
a45
b15
c40


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

ax2
ax4
.............................................
adx4x45=1.8
ady3x45=1.35
ady5x45=2.25
adz5x45=2.25
bdx4x15=0.6
bdy3x15=0.45
bdy5x15=0.75
bdz5x15=0.75
cdx4x40=1.6
cdy3x40=1.2
cdy5x40=2
cdz5x40=2

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

Thanks in advance,

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

4 Replies
swuehl
MVP
MVP

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;

bilionut
Contributor III
Contributor III
Author

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;

Anonymous
Not applicable

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;

bilionut
Contributor III
Contributor III
Author

Thank you verry much Robin for correcting script