13 Replies Latest reply: Sep 18, 2012 6:41 AM by Luis Menendez RSS

    Avoid to expand data to work with dates

    Luis Menendez

      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

        • Re: Avoid to expand data to work with dates
          Henric Cronström

          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
              Luis Menendez

              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
                Luis Menendez

                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
                Luis Menendez

                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
                  Luis Menendez

                  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