Data from two date ranges shown side by side in table?
Data from two date ranges shown side by side in table?
cellerby
Posts: 8Questions: 1Answers: 0
I am building a reporting tool that allows users to define two date ranges, and then review a report that shows data for both date ranges side by side.
The challenge is that it's possible that data for the first date range may not have a counterpart in the second date range, and data in the second date range may not have a counterpart in the second date range. So sometimes either the left or right column in a row would be blank.
Here's a quick example, where A and B represent data from different date ranges.
Make | Model | Miles A | Gas A | Miles B | Gas B
Chev | Tahoe | 2123 | 300 | 2321 | 355
Ford | Mustang | 2412 | 255 | 2141 | 223
Toyota | Prius | blank | blank | 234 | 20
Honda | Civic | 3223 | 344 | blank | blank
it's possible that a user may define a date range A that has no date for a specific make/model, and a date range B that does have data fro make/model. In which case the left side of the table (Miles A, Gas A) would be blank.
And...
it's possible that a user may define a date range B that has no date for a specific make/model, and a date range A that does have data fro make/model. In which case the right side of the table (Miles B, Gas 
would be blank.
The challenge is that I have 2 data sets, one for each date range.
If I generate a table based on the data for the first date range and append the second date range values, I will not have table rows for items that were only in the second date range.
Each of my 2 data set is an array of objects for a single date range. And the data needs to line up by Make and Model, so any merging needs to take into account that there is only 1 row for each Make/Model combination, and each Make/Model combination only shows up once in the the data for the A or B date ranges (or sometimes not at all)
I'm just having trouble wrapping my head around merging the two data sets based on matching Make/Model values, while accounting for the fact that a Make/Model may be missing in either data set.
Any advice would be greatly appreciated!
/Chris
Answers
Here's one concept that would work if I could figure out how to pull it off.
Take the 2 original arrays, and sort them by make/model and add blank values when an array is missing a make/model that the other has. This way both arrays share the same make/model indexes, and I could easily display everything side by side when building the table.
Original arrays
Date 1
[
{ make:'honda', model:'accord', miles:'324',gallons:'44' },
{ make:'honda', model:'civic', miles:'134',gallons:'21' },
{ make:'chev', model:'tahoe', miles:'123',gallons:'20' },
{ make:'toyota', model:'prius', miles:'324',gallons:'12' }
]
Date 2
[
{ make:'chev', model:'tahoe', miles:'123',gallons:'20' },
{ make:'ford', model:'mustang', miles:'234',gallons:'42' },
{ make:'honda', model:'civic', miles:'134',gallons:'21' },
{ make:'ford', model:'f150', miles:'424',gallons:'61' }
]
Resulting arrays (sorted by make/model with blank values for make/model that are only in their counterpart array)
Date 1
[
{ make:'chev', model:'tahoe', miles:'123',gallons:'20' },
{ make:'ford', model:'mustang', miles:'',gallons:'' },
{ make:'ford', model:'f150', miles:'',gallons:'' },
{ make:'honda', model:'accord', miles:'324',gallons:'44' },
{ make:'honda', model:'civic', miles:'134',gallons:'21' },
{ make:'toyota', model:'prius', miles:'324',gallons:'12' }
]
Date 2
[
{ make:'chev', model:'tahoe', miles:'123',gallons:'20' },
{ make:'ford', model:'f150', miles:'424',gallons:'61' },
{ make:'ford', model:'mustang', miles:'234',gallons:'42' },
{ make:'honda', model:'accord', miles:'',gallons:'' },
{ make:'honda', model:'civic', miles:'134',gallons:'21' },
{ make:'toyota', model:'prius', miles:'',gallons:'' }
]
Just not sure how to go about that.
/Chris
Can anyone help on this? Really stumped, and any help would be greatly appreciated.
/Chris
@Chris, I might understand this more quickly if I knew what your data source looks like. If it's a database, can you show the relevant part of the schema?
Hi @tangerine, thanks for responding.
The data source is 2 arrays containing objects, much like I depicted above. None of that can change, as they are produced from 2 separate API calls to an outside resource and have other dependancies.
Each array is populated with objects that have a number of report values that will be displayed in multiple columns, but they need to be matched up in rows by 2 properties, in this example I'm using "make" and "model."
The easiest solution for me to work with would be if both arrays could be processed to sort by make/model so their indexes line up, and fill in blank spaces in either array with blank objects to account for items that are only in one of the two arrays.
I'm not sure how great a job I'm doing explaining this, so please let me know if there is anything I can clarify.
And thanks again!
/Chris
I created a sample based on my understand of what you want.
http://live.datatables.net/veduzaze/1/edit
It starts with a blank array, called
data, then iterates through Date 1 and adds to the array, creating an index ofmake + model. Before appending each Date 1 object it checks to see if it exists in the array. This check might not be needed as with the data above it will always be true and append.I then does the same with Date 2. If the
make + modelis not found then it adds the Date 2 object, clearing the miles and gasoline properties and creating themake + modelkey.The data can be added to the table during Datatables initialization (which I have commented out) or it can be added using
rows.add(). Usingrows.add()may work better depending on the timing of getting and processing the data compared to initializing Datatables.This sample results in one table having the following data:
Maybe this will give you a starting point. Feel free to change as desired.
Kevin
Hi @kthorngren,
The output needs to be 2 sets of data that line up, so the final report could have "Miles 1", "Miles 2", "Gas 1", and "Gas 2" in each row, where "1" and "2" represent each of the date ranges for a make/model combination.
The goal is a report that allows side-by-side comparison of miles and gas for each make/model combination over 2 date ranges.
Having the end result be 2 arrays (one for each date range) where the indexes lined up for matching make/model combinations that would give me a lot of flexibility when building the table. (among other things)
/Chris
That would take some changes to the table and loops:
Table:
- Add two columns for Miles 2 and Gas 2
- Rename Miles and Gas to Miles 1 and Gas 1
Date 1 loop:
- Assign the Miles and Gas fields to Miles 1 and Gas 1
- Assign
""to Miles 2 and Gas 2- Append to
dataDate 2 loop:
- If index (
make + model) exist indatathen just assign Miles and Gas todata[index].miles2anddata[index].gas2.- Otherwise add to
datawithdata[index].miles1anddata[index].gas1both assigned""and Miles and Gas assigned todata[index].miles2anddata[index].gas2I think, at a high level, that should meet your needs.
Kevin
Does that account for scenarios where the left date range has no counterpart in the right date range, and the right date range has no counterpart in the left date range? IE: A blank on the left side, or a blank on the right side?
Thanks again for your time on this!
/Chis
Good question. I haven't coded it to try. Thats an exercise for the reader
My sample was meant to show you that your arrays can be processed into a structure for Datatables to use and then how to apply the result to Datatables.
My suggestion above, if coded properly, would populate the data with all of the left date range with blanks for the right miles and gas cells. Then when processing the right date range, If the index exists, the existing left data would be updated with only the right miles and gas. Otherwise it will append the right to the data with the left miles and gas cells blank.
Kevin
I feel like this may be the right direction, but after a few hours of working on it I'm not seeing the light at the end of the tunnel. I'm trying to get it to work in a way that produces a comparison between 2 date ranges as mentioned above, but having no luck yet. I've not really worked with mapping before.
I've tried to work with your instructions for modifying the code example, but I can't quite get it to work as desired.
I have it working showing showing data for just the Date 1, or just the Date 2, but not for both Date 1 and Date 2 at the same time.
My problem seems to be in the data 2 loop
Example:
http://live.datatables.net/veduzaze/2/edit
Defining miles1 or miles 2 in the data2 loop doubles up the makeModel (2 tahoe, for example)
And no data shows up on the right Date 2 columns, since it ends up in the left Date 1 columns with doubled makeModels.
/Chris
Cool, thanks for trying!!
Check out this update:
http://live.datatables.net/veduzaze/3/edit
Same basic structure but I just simply add the left date (Date 1) to the
datawith the generated index key of make + model and blank miles2 and gallons2 values for the right date.The loop for Date 2 tries to get the make + model key of Date 2 from
data. If the object exists then update the object's miles2 and gallons2 fields with the Date 2 miles and gallons values. If it doesn't exist then add Date 2 first setting the index key and miles2 and gallons2 fields equal to the respective miles and gallons values. Then clear the miles and values gallons and append todata.Also changed the columns to this:
This way we don't have to change anything with the left date fields. But we need to move the right date miles and gallons fields to miles2 and gallons2.
I updated the miles and gallons data for the Date 2 array by adding one to the leading digit. This way its easy to know everything is working or not.
HTH,
Kevin
Kevin,
That seems to do the trick nicely.
Thank you so much for all the help, you are a hero!
/Chris