2 Replies Latest reply: Aug 10, 2013 5:08 PM by Paul Scotchford RSS

    Load extra data to the fact

    Paul Scotchford

      Here's my problem, thanks in advance to all contributors...

       

      I load a Fact with say 12 Months of Daily sales rows up to say today (10/8/2013) (my MaxDate that is).

      I load my daily budgets to the fact table using concatenate, it loads daily budgets to (Max date) to today (Max date)

       

      Thats all good , but ...

       

      I want budgets to load for the Full current month (say August).

       

      How can I force the load script to do this (is it a simple as coding my SQL query to extract the extra days rows in the budget table)

      or should I load my Calendar to the Max day for the current month even though sales is to today (I would expect a calendar

      with future dates would throw out my day on day expressions )?

       

       

      Thankyou

       

      Paul

        • Re: Load extra data to the fact
          Stefan Wühl

          Could you post your current script?

           

          I assume you are using a variable you set to today() to limit your records, something like this:

           

          Let vEndDate = Today();

           

          LOAD * FROM ... WHERE DateField <= '$(vEndDate)';

           

          Is this assumption correct?

           

          You can extend your records until monthend like

           

          Let vEndDate = Date(Monthend(Today()));

           

           

          [To make this work in your script, you probably need to take care of the DateField date format by specifying the format code in Date(), or setting it as DateFormat environment variable].

           

          I would also assume that you need to extend your calendar to the month end, too, but I know not enough about what your requirements and current implementation look like.

            • Re: Load extra data to the fact
              Paul Scotchford

              Thanks for your reply, I have an idea that I could extend the calendar to the end of the month and flag the calendar row that is greater than today as a budget only date.

              Im not in the office at the moment, but will extend on this idea, Im new to QV and still in my learning curve.

              I'll post my solution here to share if it delivers what I want

              Thanks

              Paul