topic Re: Calculating a new % field within the LOAD statement in QlikView

Calculating a new % field within the LOAD statement

mattparker — Wed, 17 Jan 2018 22:18:01 GMT

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;

Re: Calculating a new % field within the LOAD statement

Anil_Babu_Samineni — Thu, 18 Jan 2018 01:02:42 GMT

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;

Re: Calculating a new % field within the LOAD statement

sunny_talwar — Thu, 18 Jan 2018 01:06:01 GMT

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;

Re: Calculating a new % field within the LOAD statement

mattparker — Thu, 18 Jan 2018 17:27:34 GMT

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