Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET:
**REGISTER NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: distribute value in load

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

bilionut

Contributor III

2017-03-11
04:03 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

distribute value in load

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,

976 Views

1 Solution

Accepted Solutions

swuehl

MVP

2017-03-11
04:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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;

4 Replies

swuehl

MVP

2017-03-11
04:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2017-03-11
04:53 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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;

850 Views

Anonymous

Not applicable

2017-03-11
05:17 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2017-03-11
07:15 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thank you verry much Robin for correcting script

850 Views