How to get quantity based on the movements type

[ravindraa]

Hi all,

  I have struck one place in one of my requirments please give me any suggesion on this to come out from that.

My requierment is i have to caliculate the Quantity value based on the "Movement types" , but more movementtype  ae there i have to filter the quantity based on the Movement type.

Plant	Movementype( +ve)	Movemnttype (-ve)
A11	11	12
A22	11	12
A33	11	13
M11	21	12
R11	31	32
T11	11	12

above are the movement types which are there in database i have to caliculate quantity based on the movement type.

ex:

Plant     Movementtype    Quantity

A11             11                   10

A11              12                   5

here the caliculation is like i wrote in frentend  if(plant='A11',sum({<movementtype={"11"}>}quantity*1)+sum(sum({<movementtype={"11"}>}quantity*-1)                          Result is   :: 5

it is giveing exact values.

but like these there are many plants i have to applay same logic for all things, some plants have same movements type to get quantity.

please suggest how to write in back end isteated of frent end.

Please find the attach ments.

Thanks in advance..

[tresesco]

Try cosstable load like:

CrossTable([Movementype( +ve)], Quantity)

LOAD Plant,

     [Movementype( +ve)],

     [Movemnttype (-ve)]

FROM <>:

[ravindraa]

Hi Tresesco ,

  Movementtype+ve and movementtype-ve are not direct fields.

Movement type are one field in that many movementtype are there in data base (11,12,21,31,32,10,15,......etc) but i have to cosider Perticular movemetn type is +ve and some other is -ve for perticular PLANT. like wise i have saparate out those movement types to get the Quantity.

Please check the my attachment application and Excel file.

kindly give me any suggesion, that will help me alot.

thanks in advance/

[jagan]

Hi,

Try like this

Plant:

LOAD Plant,

     MovementType,     

     Quantity

FROM

[testfile.xlsx]

(ooxml, embedded labels, table is Sheet1);

//Mapping table to get the multiplication factors

LEft JOIN(Plant)

LOAD

*

INLINE [ Plant, MovementType, MultplicationFactor

A11, 11, 1

A11, 12, -1

A22, 11, 1

A22, 12, -1

A33, 11, 1

A33, 12, -1

M11, 21, 1

M11, 12, -1

R11, 31, 1

R11, 32, -1

T11, 11, 1

T11, 12, -1

];

Chart : Straight Table

Dimension: Plant

Expression:Sum(Quantity * MultplicationFactor)

Hope this helps you.

Regards,

jagan.

[tresesco]

In that case, maintain a mapping table that holds the flagging of +ve/-ve and use that flag as a part of set analysis to decide what to add and subtract.

[ravindraa]

Hi JaganMohan,

  Thank you very much for replying but i need that caliculation field(sum(Quantity*MultiplicaitonFactor)) in backend  because i have to use same field in some toher where, i mean i have to display the data based on the 3 months and 6 months YTD quantity consumption  like caliculation.

Please give me any suggesion.

Thanks in advance.

[jagan · Accepted solution]

Hi,

Try this script

PlantTemp:

LOAD Plant,

     MovementType,    

     Quantity

FROM

[testfile.xlsx]

(ooxml, embedded labels, table is Sheet1);

//Mapping table to get the multiplication factors

LEft JOIN(PlantTemp)

LOAD

*

INLINE [ Plant, MovementType, MultplicationFactor

A11, 11, 1

A11, 12, -1

A22, 11, 1

A22, 12, -1

A33, 11, 1

A33, 12, -1

M11, 21, 1

M11, 12, -1

R11, 31, 1

R11, 32, -1

T11, 11, 1

T11, 12, -1

];

Data:

LOAD

*,

Quantity * MultplicationFactor AS FinalValue

RESIDENT PlantTemp;

DROP TABLE PlantTemp;

Regards,

Jagan.

[jagan]

Now Sum(FinalValue) as your expression.

Regards,

Jagan.

[ravindraa]

Thank you very much Jagan Mohan,

  Now it is working fine thank you very much again once.

[ravindraa]

Thank you very much Tresesco,

  Now it is working fine again thank you very much.