Ayuda! Suma varios condicionales

[#1]

Hola amigos,

Espero que puedan ayudarme con esta consulta....

Tengo dos tablas:

La primera contiene Código Producto - Fecha de Orden - Cantidad - Monto. (Esta tabla muestra las ordenes pendientes de entrega)

La otra tabla tiene Código Producto - Cantidad_Ingreso

La segunda tabla me informa lo que va a ingresar en un periodo, me dice que código y cuanta cantidad.

Lo que necesito es que busque cada codigo incluido en la 2da tabla, y lo encuentre en la primera, pero sume solamente los montos comenzando desde la menor fecha hasta la mayor o hasta cumplir con las cantidades que estiman que ingresen (dato de la tbala 2).

Por ej. si en la tabla dos, yo tengo que va a ingresar 3u del cod:001.

Y tengo 5 ordenes penidentes x 1u cada una a un monto distinto, necesitaria que me devuelva solo los montos de 3u (ya que es lo unico que ingresará) y comenzando desde la menor fecha hasta la mayor...

No encontré forma, si alguno puede ayudarme se los agradecría enormemente!

Saludos

[IAMDV]

Hola compañero,

No sé español! Yo trato de entender su pregunta a través transalation. Pero es muy claro ... será grande si usted puede enviar su pregunta en Inglés. Además, sería muy fácil si usted puede proporcionar datos de ejemplo de la Tabla 1 y Tabla 2.

Cheers,

DV

www.QlikShare.com

[#3]

Hi Deepak!

I tried to translate the best I could my english its not so good .

I have two tables:

the first one contains:

Product_Code - Order date - Quantity - Amount. (This table shows the actual undelivered orders).

The other table has Product_Code – estimated_Quantity. (This second table tells the quantity for each “product_code” what will be available on stock in a few days).

So, with the information of the second table I may predict the orders that y could be able to dispatch and the "amount" of these.

So, what I need is to look up each code included in the 2nd table, and “SUM” only the amounts starting from the older to the nearest date (because we have to dispatch the oldest orders first).

Eg: If a product has a estimated_quantity of 3units (in a few days we will recived 3 units of that code) and on the first table we have 5 diferent orders for that code of 1 unit each, I have to consider only the 3 oldest on the “SUM” of amounts.

Hope you could understand me!

Thanks for your help!

regards

[IAMDV]

Thanks fedetoso. You need to solve in this in the script. I'm attaching an example for your reference but it's incomplete. Sorry, I couldn't finish it because I need to rush out for dinner. See the attached and I'm sure that will give you the idea on how to solve it.

Good luck!

Cheers,

DV

www.QlikShare.com

[IAMDV]

fedetoso

Try to calculate the Accum_Amount & Accum_Quantity in the script and use Set Analysis to get the Accum_Amount for Estimated_Quantity based on Accum_Quantity. I hope that makes sense.

Cheers,

DV

www.QlikShare.com

[#6]

Deepak,

What I need is (taking your table´s as example)

Table1:

LOAD * INLINE [

    Product_Code,  Order Date, Quantity, Amount

    A1, 20-Jun-2013, 1, 100

    A1, 21-Jun-2013, 2, 150

    A1, 22-Jun-2013, 1, 100   

    A1, 23-Jun-2013, 1, 150       

    A1, 24-Jun-2013, 1, 150           

    A1, 25-Jun-2013, 1, 150

    A2, 22-Jun-2013, 1, 100   

    A2, 23-Jun-2013, 2, 200       

    A2, 24-Jun-2013, 1, 250           

    A2, 25-Jun-2013, 1, 250                                   

];

Table2:

LOAD * INLINE [

Product_Code, Estimated_Quantity

A1, 3

A2, 4

];

In this case they will estimated on futur stock 3 units of product A1 and 4 of A2, so if I "sum" amounts starting from de oldest I just need tu see this information:

A1     3u     250$

A2     4u     550$

Just this final table. another question, in my file the dates are not in order can it works?

Because of (

  A1, 20-Jun-2013, 1, 100

    A1, 21-Jun-2013, 2, 150

and

    A2, 22-Jun-2013, 1, 100   

    A2, 23-Jun-2013, 2, 200       

    A2, 24-Jun-2013, 1, 250 

)

Regards

[IAMDV]

Hi Mate,

Not sure if the below result is correct!

A1     3u     250$

A2     4u     550$

According to my understanding it should be...

A1     3u     400$

A2     4u     750$

Because you have 3u based on the order date.

For Product A1: (1 x $100) + (2 x $150) = $400

For Product A2: (1 x $100) + (2 x $200) + (1 x $250)= $750

Is this not right? Am I missing something with the logic?

Thanks,

DV

www.QlikShare.com

[#8]

Hi Deepak,

Sorry, I forgot to mention that the column "amount" comes from a query and is the total USD amount for the quantities on that line.

I think this makes it even dificult because you may have a period with 5u on table 1 and 3u on table 2, so you must have to get the unit price and then multiplied to the other 3u (amount/5u)*3u.

Beside this difference, the only final table y need to see its just this:

A1     3u     250$

A2     4u     550$

Thanks again for your help!

Regards

[IAMDV]

Hi Mate,

Please see if the attachment makes sense... I got the expected results but we need to take care of the second scenario you mentioned above..

fedetoso wrote:

I think this makes it even dificult because you may have a period with 5u on table 1 and 3u on table 2, so you must have to get the unit price and then multiplied to the other 3u (amount/5u)*3u.

Beside this difference, the only final table y need to see its just this:

A1     3u     250$

A2     4u     550$

Cheers,

DV

www.QlikShare.com

[#10]

thanks!!!!!