Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2012-09-13 12:17 PM

Avoid to expand data to work with dates

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):

HOTELMMCDOTOTTOOIDCTOFIPEFFPETHABASE
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA75
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA77

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:

HOTELMMCDOTOTTOOIDCTOFIPEFFPETHABASEFECHA
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7501/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7502/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7503/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7504/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7505/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7506/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7507/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7508/01/2012
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7701/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7702/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7703/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7704/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7705/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7706/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7707/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7708/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7709/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7710/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7711/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7712/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7713/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7714/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7715/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7716/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7717/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7718/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7719/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7720/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

Preview file
12 KB
1,091 Views
0 Likes
1 Solution

Accepted Solutions
hic
Former Employee
Former Employee
‎2012-09-15 04:34 AM

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

View solution in original post

729 Views
0 Likes
13 Replies
hic
Former Employee
Former Employee
‎2012-09-15 04:34 AM

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

730 Views
0 Likes
Not applicable
‎2012-09-17 07:32 AM
Author

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

729 Views
0 Likes
Not applicable
‎2012-09-17 07:33 AM
Author

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

729 Views
0 Likes
Not applicable
‎2012-09-17 07:36 AM
Author

In response to hic

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

729 Views
0 Likes
Not applicable
‎2012-09-17 07:36 AM
Author

In response to hic

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

729 Views
0 Likes
hic
Former Employee
Former Employee
‎2012-09-17 07:59 AM

In response to

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

729 Views
Not applicable
‎2012-09-17 01:24 PM
Author

In response to hic

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

Preview file
25 KB
729 Views
0 Likes
hic
Former Employee
Former Employee
‎2012-09-17 04:34 PM

In response to

If you want to exclude zeros, you could use

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

or

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

HIC

729 Views
Not applicable
‎2012-09-18 02:38 AM
Author

In response to hic

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.

729 Views
0 Likes