topic Re: Ayuda! Suma varios condicionales in QlikView

Ayuda! Suma varios condicionales

Sat, 29 Jun 2013 16:13:15 GMT

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

Re: Ayuda! Suma varios condicionales

IAMDV — Sat, 29 Jun 2013 17:23:20 GMT

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,

www.QlikShare.com

Re: Ayuda! Suma varios condicionales

Sat, 29 Jun 2013 17:41:49 GMT

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

Re: Ayuda! Suma varios condicionales

IAMDV — Sat, 29 Jun 2013 20:02:39 GMT

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,

www.QlikShare.com

Re: Ayuda! Suma varios condicionales

IAMDV — Sat, 29 Jun 2013 20:05:12 GMT

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,

www.QlikShare.com

Re: Ayuda! Suma varios condicionales

Sat, 29 Jun 2013 21:56:37 GMT

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

Re: Ayuda! Suma varios condicionales

IAMDV — Mon, 01 Jul 2013 11:15:58 GMT

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,

www.QlikShare.com

Re: Ayuda! Suma varios condicionales

Mon, 01 Jul 2013 12:06:53 GMT

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

Re: Ayuda! Suma varios condicionales

IAMDV — Mon, 01 Jul 2013 18:09:58 GMT

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,

www.QlikShare.com

Re: Ayuda! Suma varios condicionales

Mon, 01 Jul 2013 19:56:09 GMT

thanks!!!!!

Re: Ayuda! Suma varios condicionales

IAMDV — Mon, 01 Jul 2013 21:50:09 GMT

My pleasure mate. I shall try rest of it tomorrow or soon. Good luck!

Cheers,