Lookup and Summarize

Question

So i have 2 datasets, one that is a master data set that has a daily listing of hours and i need to compare this to my billing data, which summarizes the hours between various period.

How do take the master data and summarize by unit# between the date periods to compare if the hours match? I am a little stumped because a summarize will not work and i'm fresh out of ideas.

Data is attached

Data Set Lookup.yxmd

Qiu · Answer

@krishnagandhi 
I use generate Rows Tools

1005-krishnagandhi.yxmd

mst3k · Answer

it sounds like you need to join by a "date range", then sum up the values out of the first dataset to compare to the non-summed values from the second dataset.

the way to do that is to append the records, then use filter to act as the join. if you have really huge datasets you need to be careful because of the append tool. you'll have the date ranges lined up with your first dataset now, and you can add up the hours from the first dataset, to compare to the grouped values out of the second. the browse shows them side by side.

1) not all of your starting records seemed to have a matching date range in the other dataset, from what i could tell

2) your date ranges overlap (starting date of one row is same as ending date of another), so i assumed the starting date would take the "tiebreak".   starting date <=  Date  <  ending date

DatasetLookup.yxmd