topic Re: Distribute KG by Percentage for multiple dimensions in QlikView

Distribute KG by Percentage for multiple dimensions

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

I want to distribute kilograms on multiple distribution percentages of different dimensions (Categories, Size, ...).

The distribution has multiple tables with different agregations, so I think one way is to have "island" tables and multiply with the related data. But didn't work.

I've attached an excel with some example data.

1 Example: Kilograms

2 Example: % Distribution by Category

3 Example: % Distribution by Size

4 Example: Kilograms Distributed

Thanks!

Re: Distribute KG by Percentage for multiple dimensions

chrismarlow — Tue, 20 Nov 2018 15:31:35 GMT

Hi,

Now the synthetic keys in this might need some thought, but I don't think you need to use a data island;

Am I missing something?

Cheers,

Chris.

Script in case you cant open the attachment;

data:
LOAD Year,
     prod_codigo,
     factory,
     espe,
     vari,
     KG
FROM
[Distribution Example.xlsx]
(ooxml, embedded labels, table is Data);

factory:
LOAD Year,
     factory,
     espe,
     Category,
     Distrib% as factory_dist
FROM
[Distribution Example.xlsx]
(ooxml, embedded labels, table is [Category by Factory]);

vari:
LOAD Year,
     factory,
     espe,
     vari,
     Category,
     Distrib% as vari_dist
FROM
[Distribution Example.xlsx]
(ooxml, embedded labels, table is [Category by vari]);

size:
LOAD Year,
     factory,
     espe,
     vari,
     Size,
     Distrib% as size_dist
FROM
[Distribution Example.xlsx]
(ooxml, embedded labels, table is Size);

Re: Distribute KG by Percentage for multiple dimensions

cristian_av — Tue, 20 Nov 2018 15:58:20 GMT

It works for code 1113 and 1148 and don't work for code 1116 and 1117.
In the original data, it works for all.. Not sure why.

Re: Distribute KG by Percentage for multiple dimensions

chrismarlow — Tue, 20 Nov 2018 16:20:19 GMT

QlikView associates on all fields with the same name, unless you specifically rename them.

So here factory is included in the association, but for 1116 factory = 1 on Data & 3 on Category by vari and Size tabs.

You can try changing these;

vari:
LOAD
Year,
factory as factory_vari,

And;

size:
LOAD Year,
factory as factory_size,

Which looks to me like it works, but not sure what effect that would have on the rest of your model or the other ways you may wish to apportion.

Cheers,

Chris.