Rangesum but in Load Editor with 3 dimensions

[ISK]

I am trying to get results similar to Rangesum() but in the Load Editor. Here's the code that I'm using to Acheive it:

Load

    BSPL_Posting_Date,

    BSPL_Posting_Date_Monthname,

    BSPL_Posting_Date_Year,

    BSPL_Inventory_Plant_Number,

    BSPL_Inventory_Material_Number,

    BSPL_Inventory_Material_Type,

    BSPL_Inventory_Material_Group_Number,

    BSPL_Inventory_Material_Description,

    BSPL_Material_Group_Description,

    BSPL_Inventory_Stor_Loc,

    BSPL_Inventory_Unit,

    BSPL_Inventory_Currency,

    BSPL_stockinquantity,

    BSPL_stockinvalue,

    BSPL_stockoutquantity,

    BSPL_stockoutvalue,

    //if(match(BSPL_Inventory_Stor_Loc,'KHST'),'0',BSPL_Stock_Value) as BSPL_Stock_Value,

    BSPL_Stock_Value,

    BSPL_Stock_Quantity,

    BSPL_Tot_usage,

    BSPL_Tot_us_val

Resident BSPL_S033 where BSPL_Inventory_Plant_Number = '0070'

order by

BSPL_Inventory_Material_Number,

    BSPL_Posting_Date,

    BSPL_Posting_Date_Monthname,

    BSPL_Posting_Date_Year;

Drop Table BSPL_S033;

BSPL_S033_Aggregated:

Load

BSPL_Posting_Date,

    BSPL_Posting_Date_Monthname,

    BSPL_Posting_Date_Year,

    BSPL_Inventory_Material_Number,

    BSPL_Inventory_Material_Type,

    BSPL_Inventory_Material_Group_Number,

    BSPL_Inventory_Material_Description,

    BSPL_Material_Group_Description,

    BSPL_Inventory_Unit,

    BSPL_Inventory_Currency,

  Sum(BSPL_Stock_Value) as BSPL_Stock_Value,

  sum(BSPL_Stock_Quantity) as BSPL_Stock_Quantity,

Resident S033_Adjusted_1

Group by 

BSPL_Inventory_Material_Number,

    BSPL_Posting_Date,

    BSPL_Posting_Date_Monthname,

    BSPL_Posting_Date_Year,

    BSPL_Inventory_Material_Type,

    BSPL_Inventory_Material_Group_Number,

    BSPL_Inventory_Material_Description,

    BSPL_Material_Group_Description,

    BSPL_Inventory_Unit,

    BSPL_Inventory_Currency;

Drop Table S033_Adjusted_1;

NoConcatenate

BSPL_S033_Aggregated2:

LOAD

*,

If(

        BSPL_Inventory_Material_Number = Peek('BSPL_Inventory_Material_Number'),

RangeSum(Peek('Aggr_Value'),BSPL_Stock_Value),BSPL_Stock_Value) as Aggr_Value,

If(

        BSPL_Inventory_Material_Number = Peek('BSPL_Inventory_Material_Number'),

RangeSum(Peek('Aggr_Qty'),BSPL_Stock_Quantity),BSPL_Stock_Quantity) as Aggr_Qty

resident BSPL_S033_Aggregated

order by BSPL_Inventory_Material_Number,

    BSPL_Posting_Date;

drop table BSPL_S033_Aggregated;

NoConcatenate

BSPL_S033_Aggregated3:

Load

*,

rangemax(Aggr_Value/Aggr_Qty,0) as "Per Unit"

resident BSPL_S033_Aggregated2;

drop Table BSPL_S033_Aggregated2;

And here's the output that this gives:

This is working fine. All Ok. 

Now I want to Incorporate Storage Location with Material Number and it should calculate only Aggr_Qty for Each Material Number & Storage Location. For each Material there are around 3 Storage Locations.  I'm guessing I have to create a new table similar to the one above and incorporate storage location in Aggr_Qty logic. Any suggestions how to do it?

[marksouzacosta]

Hi @ISK,

I was not able to follow all your logic but, do you know the Window function? It may help you in your case:
Window - script function | Qlik Sense on Windows Help

Regards,

Mark Costa