topic Get values between dates in QlikView

Get values between dates

spividori — Fri, 20 May 2011 14:00:54 GMT

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.

Get values between dates

erichshiino — Fri, 20 May 2011 15:27:56 GMT

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

Get values between dates

Sat, 21 May 2011 08:33:53 GMT

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

Re: Get values between dates

erichshiino — Sun, 22 May 2011 14:05:56 GMT

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;

Get values between dates

spividori — Mon, 23 May 2011 13:48:22 GMT

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.

Re: Get values between dates

spividori — Mon, 23 May 2011 13:57:33 GMT

Examples!!!

Re: Get values between dates

erichshiino — Mon, 23 May 2011 17:07:33 GMT

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;

Get values between dates

spividori — Mon, 23 May 2011 20:50:33 GMT

Hi Erich.

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

Regards.

topic Get values ​​between dates in QlikView

Get values ​​between dates

Get values ​​between dates

Get values ​​between dates

Re: Get values ​​between dates

Get values ​​between dates

Re: Get values ​​between dates

Re: Get values ​​between dates

Get values ​​between dates

topic Get values between dates in QlikView

Get values between dates

Get values between dates

Get values between dates

Re: Get values between dates

Get values between dates

Re: Get values between dates

Re: Get values between dates

Get values between dates