Get values ​​between dates

[spividori]

Hi.

I need to get on the table "Costos" cost value of each items for the table Ventas.
As seen in the Resultado table.
Attached example.

Regards.

[erichshiino]

Is your cost always in the beggining of the month? You could solve that associating the sales with the price at the beggining, like this:

Ventas:

LOAD * INLINE [

    ARTICULO, FECHA_F

    1, 01/04/2011

    1, 02/05/2011

    1, 01/05/2011

    1, 12/05/2011           

    2, 01/05/2011

    2, 01/04/2011   

    3, 01/04/2011

    3, 06/05/2011

    3, 10/05/2011       

    4, 01/04/2011

    5, 01/05/2011

];

Costos:

LOAD * INLINE [

    ARTICULO, FECHA_C, COSTE

    1, 01/04/2011, 1

    1, 01/05/2011, 1.5

    2, 01/04/2011, 2

    2, 01/05/2011, 2   

    3, 01/04/2011, 3

    3, 01/05/2011, 3.1

    4, 01/05/2011, 4

    5, 01/04/2011, 4   

    5, 01/05/2011, 5

];

VentasResult:

Load ARTICULO, FECHA_F, monthstart(FECHA_F) AS MES_COSTO

RESIDENT Ventas;

drop table Ventas;

left join(VentasResult)

load ARTICULO, FECHA_C as MES_COSTO, COSTE

resident Costos;

drop table Costos;

Hope it helps,

Erich

[#3]

How to solve the case of ACTICULO 4 with month 4 in FECHA_F and month 5 in FECHA_C? or it's a mistake of this example?

Regards

[erichshiino]

HI,

I'm not sure if that was a mistake in the original data.

Someone needs to define what is the price in a case like that.

Let us say, for example that that you move the prices back, and if there is no price for april, you apply the price in may for that one.

I could create a table with all the products and all the months and fill the gaps which could generate a really big table.

I include the costs in the middle of the sales (creating a kind of fact table).

Then I used this to fill the gaps, then I remove this lines from the table (using a dummy field as a flag).

The result is here: (If you are using QV 10, please don't mind the errors indicated by the syntax checker that just doesn't recognize the 'noconcatenate' command - the script will run just fine)

Hope it helps,

Erich

Ventas:
LOAD * INLINE [
    ARTICULO, FECHA_F
    1, 01/04/2011
    1, 02/05/2011
    1, 01/05/2011
    1, 12/05/2011           
    2, 01/05/2011
    2, 01/04/2011   
    3, 01/04/2011
    3, 06/05/2011
    3, 10/05/2011       
    4, 01/04/2011
    5, 01/05/2011
];
Costos:
LOAD * INLINE [
    ARTICULO, FECHA_C, COSTE
    1, 01/04/2011, 1
    1, 01/05/2011, 1.5
    2, 01/04/2011, 2
    2, 01/05/2011, 2   
    3, 01/04/2011, 3
    3, 01/05/2011, 3.1
    4, 01/05/2011, 4
    5, 01/04/2011, 4   
    5, 01/05/2011, 5
];
tVentasResult:
Load ARTICULO, FECHA_F, monthstart(FECHA_F) AS MES_COSTO
RESIDENT Ventas;
drop table Ventas;
left join(tVentasResult)
load ARTICULO, FECHA_C as MES_COSTO, COSTE
resident Costos;
Concatenate(tVentasResult)
load ARTICULO, COSTE, FECHA_C as MES_COSTO, 1 as flagDummy
resident Costos;
noconcatenate
tempVentasResults:
Load ARTICULO, MES_COSTO, FECHA_F, flagDummy, COSTE,
IF(LEN(TRIM(COSTE))=0 AND ARTICULO=PEEK('ARTICULO'), PEEK('NUEVO_COSTE'),COSTE) AS NUEVO_COSTE
RESIDENT tVentasResult
ORDER BY ARTICULO, MES_COSTO DESC;
DROP TABLE tVentasResult;
noconcatenate
VentasResults:
load * resident tempVentasResults
where flagDummy<>1;
drop table Costos;
drop table tempVentasResults;
drop field flagDummy;

[spividori]

Hi guys.

Thank you very much for responding. I was away so it can not continue.

I was working the weekend and I could not solve.

I try to explain.

The cost is not always at the beginning of the month, can be daily, weekly, monthly or might not have.

The date of sale must take the date of cost that is equal, if this condition does not exist, should take the nearest date backwards, if this condition does not exist, should take the date immediately above.

Examples:

Regards.

[spividori]

Examples!!!

[erichshiino · Accepted solution]

Hi,

Try this script. I included some comments in the middle.

Regards,

Erich

TEMPFact:
//VENTAS
//TYPE =2 WILL MAKE THOSE LINES COME AFTER THE COST WHEN ORDERED BY DATE AND TYPE
LOAD *, 2 as TYPE INLINE [
    ARTICULO, FECHA_F
    1, 01/04/2011
    1, 02/05/2011
    1, 01/05/2011
    1, 12/05/2011           
    2, 01/05/2011
    2, 01/04/2011   
    3, 01/04/2011
    3, 06/05/2011
    3, 10/05/2011       
    4, 01/04/2011
    5, 01/05/2011
];
CONCATENATE (TEMPFact)
//COSTOS
//TYPE =1 WILL MAKE THOSE LINES COME BEFORE THE SALES WHEN ORDERED BY DATE AND TYPE
LOAD ARTICULO, FECHA_C AS FECHA_F, 1 as TYPE, COSTE INLINE [
    ARTICULO, FECHA_C, COSTE
    1, 01/04/2011, 1
    1, 01/05/2011, 1.5
    2, 01/04/2011, 2
    2, 01/05/2011, 2   
    3, 01/04/2011, 3
    3, 01/05/2011, 3.1
    4, 01/05/2011, 4
    5, 01/04/2011, 4   
    5, 01/05/2011, 5
];
//NoConcatenate
Temp2Fact: 
load ARTICULO, FECHA_F, TYPE, IF( ISNULL(COSTE), IF( ARTICULO = PEEK('ARTICULO'), PEEK('NEW_COSTE')), COSTE) AS NEW_COSTE, COSTE
//FILL CELLS WITH THE VALUE ABOVE IF IT IS THE SAME ARTICLE
RESIDENT TEMPFact 
ORDER BY ARTICULO,FECHA_F, TYPE
//ORDER BY CRITERIA IS IMPORTANT TO FILL CELLS
;
drop table TEMPFact; 
//AT THIS POINT I CAN GET THE COST THAT CAME BEFORE THE SALES
NoConcatenate
//THE SECOND STEP WILL RUN BACKWARDS TO FILL THE MISSING CELLS
Temp3Fact: 
load ARTICULO, FECHA_F, TYPE, IF( ISNULL(NEW_COSTE), IF( ARTICULO = PEEK('ARTICULO'), PEEK('NEW_COSTE2')), NEW_COSTE) AS NEW_COSTE2, COSTE, NEW_COSTE
RESIDENT Temp2Fact 
ORDER BY ARTICULO,FECHA_F desc, TYPE
;
drop table Temp2Fact;
NoConcatenate
//I WILL ELIMINATE THE COSTS LINES IN THE MIDDLE OF THE FACT TABLE
Fact:
LOAD * RESIDENT Temp3Fact
Where TYPE = 2;
DROP TABLE Temp3Fact;
DROP FIELD TYPE; 

[spividori]

Hi Erich.

Your answer is excellent!!!, thank you, thank you very much!!!

Regards.