Calculating a new % field within the LOAD statement

[mattparker]

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_number	cost	month_year	new_percentage
ABC_123	$10	January 2017	16%
ABC_124	$15	January 2017	24%
ABC_125	$12	January 2017	19%
ABC_126	$11	January 2017	17%
ABC_127	$15	January 2017	24%
ABC_123	$11	February 2017	48%
ABC_125	$12	February 2017	52%

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;

[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;

[sunny_talwar · Accepted solution]

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]

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