Rangesum but in Load Editor with 3 dimensions

ISK — Mon, 29 Jul 2024 13:09:41 GMT

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?

Re: Rangesum but in Load Editor with 3 dimensions

marksouzacosta — Wed, 31 Jul 2024 23:16:43 GMT

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

topic Re: Rangesum but in Load Editor with 3 dimensions in App Development

Rangesum but in Load Editor with 3 dimensions

Re: Rangesum but in Load Editor with 3 dimensions