topic Re: Avoid to expand data to work with dates in QlikView

Avoid to expand data to work with dates

Thu, 13 Sep 2012 16:17:53 GMT

Good morning, I am looking for a piece of script about “master calendars” or a similar solution to the following problem:

My BBDD data source generates rows or records like these (date format dd/mm/yy):

HOTEL	M	MCDO	TO	TTOO	ID	CTO	FIPE	FFPE	THA	BASE
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77

Where the Price (BASE) is 75 y 77 for the periods included between FIPE (Begining Period Date) and FFPE (Ending Period Date) respectively, that means, for the first record or row the valid price (BASE) is 75 during 8 days and for the second record the valid price is 77 during 20 days.

Summarizing, in order to analyze Price averages for differents periods etc the only way I found was to join a new column called FECHA (DATE) and transformate the original BBDD table in this other:

HOTEL	M	MCDO	TO	TTOO	ID	CTO	FIPE	FFPE	THA	BASE	FECHA
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	01/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	02/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	03/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	04/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	05/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	06/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	07/01/2012
1	ALE	ALEMANIA	AIM	THOMAS COOK AG (AIM)	8213	AIMTULS12	01/05/2012	08/05/2012	HA	75	08/01/2012
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	01/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	02/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	03/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	04/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	05/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	06/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	07/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	08/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	09/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	10/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	11/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	12/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	13/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	14/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	15/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	16/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	17/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	18/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	19/11/2011
1	ALE	ALEMANIA	BUCHER	THOMAS COOK AG (BUC)	3614	BUCHERTULW11	01/11/2011	20/11/2011	HA	77	20/11/2011

This way I can deal with dates, data and formulas with total flexibility thanks to the new field FECHA (DATE), but is unbearable for access or excel.

Could please anyone suggest me a solution or advice in QV script code to work with dates without having to expand the BBDD data source?

Thank you

Re: Avoid to expand data to work with dates

hic — Sat, 15 Sep 2012 08:34:53 GMT

You need to expand the date ranges, but you do not need to join the date ranges onto the BBDD table:

BBDD:

LOAD * FROM [ejemplo.xlsx]

(ooxml, embedded labels, table is [DATA_CON_20120905-172847])

where recno() <= 2;

DateRanges:

Load distinct

FIPE,

FFPE,

Date(FIPE + iterno() -1) as FECHA

resident BBDD

While iterno() <= 1 + FFPE - FIPE;

Change the first LOAD to fit your real data. (Remove the "where recno() <= 2" clause which is just needed for your example file.) You will get a data model with a synthetic key, but this is not a problem - it is instead the most efficient data model.

HIC

Re: Avoid to expand data to work with dates

Mon, 17 Sep 2012 11:32:32 GMT

Thank you very much, it is exactly what I was looking for.

One more question about this please. I think the function "=Avg(BASE)" is giving me the amount of (75+77)/2=76. How could I get a ponderate average according to the FECHA range I could select in designer dynamically?

For example, if I am analysing a period FFPE-FIPE=24 involving,

4 days for 75 ,

8 days for both prices

and 12 days 77

Where the right result should be:

(75x4 + ((75+77)/2)x8 + 77x12) = 76,33333

Thanks

Re: Avoid to expand data to work with dates

Mon, 17 Sep 2012 11:33:09 GMT

Thank you very much, it is exactly what I was looking for.

For example, if I am analysing a period FFPE-FIPE=24 involving,

4 days for 75 ,

8 days for both prices

and 12 days 77

Where the right result should be:

(75x4 + ((75+77)/2)x8 + 77x12) = 76,33333

Thanks

Re: Avoid to expand data to work with dates

Mon, 17 Sep 2012 11:36:02 GMT

Thank you very much, it is exactly what I was looking for.

One more question about this please. I think the function "=Avg(BASE)" will give me the amount of (75+77)/2=76. How could I get a ponderate average according to the range of FECHA I could select in designer dynamically?

For example, if I am analysing a period FFPE-FIPE=24 involving,

4 days for 75 ,

8 days for both prices

and 12 days 77

Where the right result should be:

(75x4 + ((75+77)/2)x8 + 77x12) = 76,33333

Thanks

Re: Avoid to expand data to work with dates

Mon, 17 Sep 2012 11:36:26 GMT

Thank you very much, it is exactly what I was looking for.

For example, if I am analysing a period FFPE-FIPE=24 involving,

4 days for 75 ,

8 days for both prices

and 12 days 77

Where the right result should be:

(75x4 + ((75+77)/2)x8 + 77x12) = 76,33333

Thanks

Re: Avoid to expand data to work with dates

hic — Mon, 17 Sep 2012 11:59:05 GMT

In your example, BASE is found in a table with only two records. Then QlikVIew will calculate the average over these two records, i.e. Avg(BASE) = (75+77)/2

If you want to make the summation in the other table, then you need to get this field into this table. Just join the two tables (put the word "Join" in front of the second load) and you will have one table with 28 records. Now QlikView will calculate the average to (75*8+77*20)/28 which is almost what you want.

But it seems to me as if you want each day (not each record) to have the same weight, or? If so, you must use a nested aggregation - first calculate the average for each day, then average over the days. You should use the formula Avg(Aggr(Avg(BASE),FECHA))

HIC

Re: Avoid to expand data to work with dates

Mon, 17 Sep 2012 17:24:14 GMT

Certainly make the join load improve my understanding of the model. With the field 'FECHA' as a part of the main and unic table I think that a simple =avg(BASE) is producing right results except when I choose a FECHA range with no Price, in this case the average include ceros. ¿Is there any way or parameter to get an average without including ceros?

In the attached example the righr result would be average = 22,87 instead of 17,533 as a result of considering 23 BASE values <>0 instead of 30.

Thank you

Re: Avoid to expand data to work with dates

hic — Mon, 17 Sep 2012 20:34:35 GMT

If you want to exclude zeros, you could use

Avg(if(BASE<>0,BASE))

Sum(BASE) / Count(if(BASE<>0,BASE))

HIC

Re: Avoid to expand data to work with dates

Tue, 18 Sep 2012 06:38:17 GMT

Perfect, now I think I have to create a master calendar linked to FECHA in order to compare months of different years an so on.

Thank you very much.

Re: Avoid to expand data to work with dates

Tue, 18 Sep 2012 06:38:41 GMT

Perfect, now I think I have to create a master calendar linked to FECHA in order to compare months of different years an so on.

Thank you very much.

Re: Avoid to expand data to work with dates

Tue, 18 Sep 2012 10:00:09 GMT

Please one last question,

I made the master calendar and I see that the first date of 'FECHA' it takes is not the very first date of all, I assume it is because I am using 'peek' without having ordering BBDD by FECHA.

I tried to put ...FROM....ORDER BY FECHA but at that line of the script 'FECHA' do not exists yet so it gives me script error.

How could I order by 'FECHA' to make the mastercalendar correctly?

Thanks

Re: Avoid to expand data to work with dates

hic — Tue, 18 Sep 2012 10:17:13 GMT

you use the following lines to find the first and last date:

let varmindate = num(Peek('FECHA',0,'BBDD'));

let varmaxdate = num(Peek('FECHA',-1,'BBDD'));

I suggest you replace these by

MinMaxDate:

Load Min(FECHA) as MinFECHA, Max(FECHA) as MaxFECHA resident BBDD;

let varmindate = num(Peek('MinFECHA',-1,'MinMaxDate'));

let varmaxdate = num(Peek('MaxFECHA ',-1,'MinMaxDate'));

Drop Table MinMaxDate;

HIC

Re: Avoid to expand data to work with dates

Tue, 18 Sep 2012 10:41:56 GMT

It seems there is something wrong with 'varmaxdate', it takes value <NULL> whereas varmindate is 40180 which is right.