4 Replies Latest reply: Dec 20, 2010 2:29 PM by John Witherspoon RSS

    Combining AsOfDate with "normal" date

    mauich123

      Hi!

      I'm loading values from a qvd-table amongst others: Qty and Sales and Date. I also have an AsOfDate-table that gives me accumulated value.

      The problem is that, what I want to do is, I want to load the Qty field and bind it to the AsOfDate-table so the value becomes accumulated when reading it from an AsOfDate. But the Sales-field I don't want to do this with. I understand that I have to load my qvd-file twice into two diffrent tables (one with Qty and one with Sales) and name the Date-field different names in each table with the one in the Qty-table being keyed to the AsOfDate-table.

      But, I also want the Qty-table and Sales-table to be keyed by date, so I can get both the Sales (not accumulated) and the Qty (accumulated) togeter.

      Whats a good way to solve this? Examples?

      Thanks!

        • Combining AsOfDate with "normal" date
          Tanel Rüütli

           

          I think a good way is to keep straight data model and calculate any accumulations within charts.

          As for example, this expression calculates accumulation in pivot table from right to left:

          RangeSum( before( sum(Qty), 0, ColumnNo() ) )



          • Combining AsOfDate with "normal" date
            John Witherspoon

            If I understand the problem, just put a DateType field in your AsOf table. Values 'current' and 'accumulated' for instance. Current would only link an AsOfDate to the same Date, while accumulated would link to all dates you're trying to accumulate. Then:

            sum({<DateType={'current'}>} Sales)
            sum({<DateType={'accumulated'}>} Qty)

            Now you can put them both in the same table with dimension AsOfDate, and Sales will only be for the current day, while Qty will be summed across all days in the desired accumulation.

              • Combining AsOfDate with "normal" date
                mauich123

                 


                John Witherspoon wrote:
                If I understand the problem, just put a DateType field in your AsOf table. Values 'current' and 'accumulated' for instance. Current would only link an AsOfDate to the same Date, while accumulated would link to all dates you're trying to accumulate. Then:
                sum({<DateType={'current'}>} Sales)
                sum({<DateType={'accumulated'}>} Qty)
                Now you can put them both in the same table with dimension AsOfDate, and Sales will only be for the current day, while Qty will be summed across all days in the desired accumulation. <div></div>


                Okay! I think I understand the jist of this idea, but how would I implement this DateType field in the script exactly?

                My AsOfDate looks like this (I think it was you who gave me this idea to use AsOf btw :)

                AsOf:
                LOAD Date
                RESIDENT Transactions;
                LEFT JOIN (AsOf)
                LOAD Date as AsOfDate
                RESIDENT AsOf;
                INNER JOIN (AsOf)
                LOAD *
                RESIDENT AsOf
                WHERE AsOfDate >= Date;

                  • Combining AsOfDate with "normal" date
                    John Witherspoon

                    Something like this sticking with roughly your current approach:

                    AsOf:
                    LOAD DISTINCT Date, 'accumulated' as DateType
                    RESIDENT Transactions;
                    LEFT JOIN (AsOf)
                    LOAD Date as AsOfDate
                    RESIDENT AsOf;
                    INNER JOIN (AsOf)
                    LOAD *
                    RESIDENT AsOf
                    WHERE AsOfDate >= Date;

                    CONCATENATE (AsOf)
                    LOAD DISTINCT Date, 'current' as DateType, Date as AsOfDate
                    RESIDENT Transactions;

                    But something like this should be more efficient, which would matter if your Transactions table is large (this probably has some syntax or other error somewhere since it's complicated and I'm not testing it):

                    Calendar:
                    LOAD date(fieldvalue('Date',iterno())) as Date
                    AUTOGENERATE 1
                    WHILE len(fieldvalue('Date',iterno()))
                    ;
                    MinDate:
                    LOAD min(Date) as MinDate
                    RESIDENT Calendar
                    ;
                    LET vMinDate = peek('MinDate')
                    ;
                    AsOf:
                    LOAD
                    Date as AsOfDate
                    ,'accumulated' as DateType
                    ,date(Date+1-iterno()) as Date
                    RESIDENT Calendar
                    WHILE date(Date+1-iterno()) >= $(vMinDate)
                    ;
                    CONCATENATE (AsOf)
                    LOAD
                    Date as AsOfDate
                    ,'current' as DateType
                    ,Date
                    RESIDENT Calendar
                    ;
                    DROP TABLE MinDate
                    ;

                    Loading distinct dates from our transactions table can be a very slow process, and the first script does it twice. In the second script, we instead load a Calendar using fieldvalue() to get the distict values, which is much faster. Then we use this calendar directly, so we don't even have to load fieldvalue() twice. For the AsOf table, instead of joining to itself and throwing out half the values afterwards, we determine a minimum date, and then iterate back until we hit that minimum date, so never have to load rows only to delete them later. Not sure if that saves any time, but it might, and it almost certainly saves memory. So just an option if your transaction table is large enough for it to be a problem with the first script.

                    Hmmm, actually, I'm not happy with that script. When it loads fieldvalue(), it is basically assuming that you do NOT have a transaction for every date. But when it loads the AsOf table, the iteration there is basically assuming that you DO have a transaction for every date. If it were me, I'd probably force every date to be in the calendar by setting up a vMinDate and vMaxDate, then iterating between them to generate all dates. Then I'd build the AsOf from THAT calendar instead of just from transaction dates. But if your data is sparse, that could be wasteful. Anyway, I'm not going to write script for every possible scenario. Hopefully there are at least enough tricks evident in the second script for you to experiment to get what you want.