Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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