Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

adrianbuzer
Contributor II

Creating proportion field in script

Hi

I have a table similar to the following which shows the supply of products by 3 suppliers and the amount they supplied each week.

WeekSupplierAmount
1A451
1B101
1C594
2A459
2B921
2C66
3A255
3B772
3C579
4A441
4B32
4C0
5A336
5B171
5C798

I want to convert that into proportions so that I can use them for forecasting purposes later.  Basically the proportion of each weeks supply by each supplier (each week will sum to 1).  So I need to create a field like this

WeekSupplierProportion
1A0.393543
1B0.088133
1C0.518325
2A0.317427
2B0.636929
2C0.045643
3A0.15878
3B0.480697
3C0.360523
4A0.932347
4B0.067653
4C0
5A0.257471
5B0.131034
5C0.611494

How can I achieve this in the script?

Thanks

A

1 Solution

Accepted Solutions

Re: Creating proportion field in script

You can use the following script:

Table:

LOAD Week,

    Supplier,

    Amount

FROM

[https://community.qlik.com/thread/164567]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD Week,

  Sum(Amount) as TotalAmount

Resident Table

Group By Week;

Table1:

LOAD Week,

  Supplier,

  Amount,

  TotalAmount,

  Amount/TotalAmount as Proportion

Resident Table;

DROP Table Table;



Output:

Capture.PNG

1 Reply

Re: Creating proportion field in script

You can use the following script:

Table:

LOAD Week,

    Supplier,

    Amount

FROM

[https://community.qlik.com/thread/164567]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD Week,

  Sum(Amount) as TotalAmount

Resident Table

Group By Week;

Table1:

LOAD Week,

  Supplier,

  Amount,

  TotalAmount,

  Amount/TotalAmount as Proportion

Resident Table;

DROP Table Table;



Output:

Capture.PNG