topic Re: How to get quantity based on the movements type in QlikView

How to get quantity based on the movements type

ravindraa — Fri, 14 Nov 2014 04:43:19 GMT

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..

Re: How to get quantity based on the movements type

tresB — Fri, 14 Nov 2014 04:50:56 GMT

Try cosstable load like:

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

LOAD Plant,

[Movementype( +ve)],

[Movemnttype (-ve)]

FROM <>:

Re: How to get quantity based on the movements type

ravindraa — Fri, 14 Nov 2014 05:18:26 GMT

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/

Re: How to get quantity based on the movements type

jagan — Fri, 14 Nov 2014 05:33:07 GMT

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.

Re: How to get quantity based on the movements type

tresB — Fri, 14 Nov 2014 05:38:24 GMT

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.

Re: How to get quantity based on the movements type

ravindraa — Fri, 14 Nov 2014 06:42:10 GMT

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.

Re: How to get quantity based on the movements type

jagan — Fri, 14 Nov 2014 09:09:22 GMT

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.

Re: How to get quantity based on the movements type

jagan — Fri, 14 Nov 2014 09:09:43 GMT

Now Sum(FinalValue) as your expression.

Regards,

Jagan.

Re: How to get quantity based on the movements type

ravindraa — Fri, 14 Nov 2014 09:49:51 GMT

Thank you very much Jagan Mohan,

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

Re: How to get quantity based on the movements type

ravindraa — Fri, 14 Nov 2014 09:50:47 GMT

Thank you very much Tresesco,

Now it is working fine again thank you very much.