Individual record for each date in range

Question

Hi,I have TABLE1 which has entries made by users as follows:&nbsp;NAME&nbsp; &nbsp; &nbsp; &nbsp; RECTYPE&nbsp; &nbsp; &nbsp; &nbsp; STARTDATE&nbsp; &nbsp; &nbsp; &nbsp; ENDDATE&nbsp; &nbsp; &nbsp; &nbsp; UTILIZATIONEMP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Leave&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100EMP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Task_RFx&nbsp; &nbsp; &nbsp; &nbsp;17-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50EMP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Task_Int&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;17-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;18-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50EMP2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Task_RFx&nbsp; &nbsp; &nbsp; &nbsp; 1-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15-Aug&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100EMP2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Leave&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 24-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100EMP3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Task_Int&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21-Jun&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100&nbsp;I want to break-up the range of each record into individual rows only for next 30 days, excluding weekends, in TABLE2 as:NAME&nbsp; &nbsp; &nbsp; &nbsp; RECTYPE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DATE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UTILIZATIONEMP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Task&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 17-Jun&nbsp; &nbsp; &nbsp; &nbsp; 100 (#Adds up utilization of all tasks)EMP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Task&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 18-Jun&nbsp; &nbsp; &nbsp; &nbsp; 100EMP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Task&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 19-Jun&nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp;(#Task_Int has ended, so only 50% utilized)EMP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Leave&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20-Jun&nbsp; &nbsp; &nbsp; &nbsp; 100&nbsp; &nbsp;(#Leave overrides all tasks)EMP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Leave&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21-Jun&nbsp; &nbsp; &nbsp; &nbsp; 100EMP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Task&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 24-Jun&nbsp; &nbsp; &nbsp; &nbsp; 50 (#weekend skipped; next record is for Monday).... Similarly for all employees&nbsp;I tried a lot, but unable to get anywhere close to how I can create this kind of table. Any help would be highly appreciated. Thanks.

douwemeer · Answer

ameyazing&nbsp;&nbsp;Easiest way would be by creating a date table and a measure. Then add a single select visual for 'NAME'. If this is your intented output.Otherwise, use something similar inside a calculated column to extract your data. You don't really need an intermediate table similar to that. Just create it on the fly per name and your done.

Forum Discussion

Individual record for each date in range

1 Reply

Related content

Sorting Records

Eliminate duplicate records while keeping the most recent record.

Counting Non-Blank Records

Letting users select individual records for export

Filtering Record History by Period