Combining AsOfDate with "normal" date

[#1]

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!

[tanelry]

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

[johnw · Accepted solution]

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.

[#4]

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;

[johnw]

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.