Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
mattparker
New Contributor III

Calculating a new % field within the LOAD statement

Hi team!  Been slamming my head into my keyboard all day trying to figure out how to even start with a problem I have...

I have a cost field for many parts over many months.  My goal is to create a new field within the load that shows what percentage of the parts' cost is for that month.

For example, I'm looking to create the new_percentage field below via the load:

part_numbercostmonth_yearnew_percentage
ABC_123

$10

January 201716%
ABC_124$15January 201724%
ABC_125$12January 201719%
ABC_126$11January 201717%
ABC_127$15January 201724%
ABC_123$11February 201748%
ABC_125$12February 201752%

The reason I need to create the percentage this way is to apply the percentage to another field (extra_cost) and add that weighted average back to the original cost to create adjusted_cost.

I would greatly appreciate any feedback anyone has to offer!  I think this should be a simple work around, but I just cannot seem to wrap my head around what needs to be done.

LOAD

     part_number,

     cost,

     month_year,

RESIDENT PartData;

1 Solution

Accepted Solutions

Re: Calculating a new % field within the LOAD statement

May be this

Table:

LOAD

     part_number,

     cost,

     month_year,

RESIDENT PartData;


Left Join (Table)

LOAD month_year,

     Sum(cost) as TotalCost

Resident Table

Group By month_year;


FinalTable:

LOAD *,

     Num(cost/TotalCost, '##.##%') as new_percentage

Resident Table;


DROP Table Table;

3 Replies

Re: Calculating a new % field within the LOAD statement

Will u able to provide the logic behind?

I have seen the output you want, but i didn't follow. Perhaps try this way

LOAD

    part_number,

    cost,

    month_year,

Avg(cost) as percentile

RESIDENT PartData group by part_number, month_year;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Calculating a new % field within the LOAD statement

May be this

Table:

LOAD

     part_number,

     cost,

     month_year,

RESIDENT PartData;


Left Join (Table)

LOAD month_year,

     Sum(cost) as TotalCost

Resident Table

Group By month_year;


FinalTable:

LOAD *,

     Num(cost/TotalCost, '##.##%') as new_percentage

Resident Table;


DROP Table Table;

mattparker
New Contributor III

Re: Calculating a new % field within the LOAD statement

I think this is exactly what I need to do.  Thank you very much, Sunny!  Very helpful!