Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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;

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!