Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mattparker
Contributor III
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
sunny_talwar

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;

View solution in original post

3 Replies
Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
sunny_talwar

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
Contributor III
Contributor III
Author

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