Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Split records - Interval Match ?

Hi all,

i have the following problem:

- I have calendar table covering the years 2011 to 2013 with one record per day

- I have a table "Budgets" with the following fields:

ID
START
END
AMOUNT
12305-04-201211-30-2012150.000,00
45601-01-201212-31-2012225.000,00

Now i want to every record of the table Budget up to get one record per workday in the period of START and END. Means, i neew something like this after the split:

ID
START
AMOUNT
12305-04-2012XX.XX
12305-07-2012XX.XX
12305-08-2012XX.XX



(XX.XX would of course be replaced by Amount/Number of weekdays in period..)

I think i first need to calculate the number of weekdays in the period. that i can do. But how would i get the table with one budget record per weekday ?

Thanks a lot for help!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

With some debugging, the FOR loop would generate the dates in the range, but a WHILE loop is easier than a FOR loop:

LOAD ID,date(Start+iterno()-1) as Date
RESIDENT Budgets
WHILE Start+iterno()-1 <= End;

But we also only want to allocate the amount to weekdays, and we do need to actually allocate the amount.  One way to do it is like this:

AllocatedBudgets:
LOAD *
WHERE weekday(Date)<5
;
LOAD ID, date(Start+iterno()-1) as Date
RESIDENT Budgets
WHILE Start+iterno()-1 <= End
;
LEFT JOIN (Budgets)
LOAD ID, count(Date) as Weekdays
RESIDENT AllocatedBudgets
GROUP BY ID
;
LEFT JOIN (AllocatedBudgets)
LOAD ID, PeriodBudget/Weekdays as AllocatedBudget
RESIDENT Budgets
;

See attached.

Edit: You could use networkdays() to establish Weekdays during the initial load of the Budgets table, or use it as the join instead of count(Date).  I think the count(Date) approach is a little more robust, as you're guaranteed to have the same number, where the networkdays() to me leaves you a little more open to things being out of sync.  Also, if you want to add holidays, while you CAN do so with networkdays(), even loading the holidays from a table, it will I think be easier to do it with the count(Date) approach.  You just need an additional condition on the WHERE for not exists(Holiday,Date) once you've loaded in your Holidays.

View solution in original post

3 Replies
Not applicable
Author

Interval match is not going to work in your sample as the ranges are overlapping.

I guess the only possible solution is to use some kind of loop.

TestTable:

LET vBudgetRows = NoOfRows('Budgets');

FOR i = 0 to $(vBudgetRows) - 1

    LET vBudgetID = Peek('ID', $(i), 'Budgets');

    LET vStart = Peek('START', $(i), 'Budgets');

    LET vEnd = Peek('END', $(i), 'Budgets');

    LOAD $(vBudgetID) as [ID], Date($(vStart) + RecNo()) as [Date] autogenerate $(vEnd) - $(vStart)

NEXT i

johnw
Champion III
Champion III

With some debugging, the FOR loop would generate the dates in the range, but a WHILE loop is easier than a FOR loop:

LOAD ID,date(Start+iterno()-1) as Date
RESIDENT Budgets
WHILE Start+iterno()-1 <= End;

But we also only want to allocate the amount to weekdays, and we do need to actually allocate the amount.  One way to do it is like this:

AllocatedBudgets:
LOAD *
WHERE weekday(Date)<5
;
LOAD ID, date(Start+iterno()-1) as Date
RESIDENT Budgets
WHILE Start+iterno()-1 <= End
;
LEFT JOIN (Budgets)
LOAD ID, count(Date) as Weekdays
RESIDENT AllocatedBudgets
GROUP BY ID
;
LEFT JOIN (AllocatedBudgets)
LOAD ID, PeriodBudget/Weekdays as AllocatedBudget
RESIDENT Budgets
;

See attached.

Edit: You could use networkdays() to establish Weekdays during the initial load of the Budgets table, or use it as the join instead of count(Date).  I think the count(Date) approach is a little more robust, as you're guaranteed to have the same number, where the networkdays() to me leaves you a little more open to things being out of sync.  Also, if you want to add holidays, while you CAN do so with networkdays(), even loading the holidays from a table, it will I think be easier to do it with the count(Date) approach.  You just need an additional condition on the WHERE for not exists(Holiday,Date) once you've loaded in your Holidays.

View solution in original post

Not applicable
Author

Mr. Witherspoon, thank you very much, this works perfectly.