Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
We moved from reading data from CSVs that were ordered by Filename to now reading straight from a SQL table.
I have a table like below:
Id | AssetId | VolumeType | VolumeReadDate | VolumeCount |
7005B82F-277F-4A33-88C9-8B81EDCA0E6F | 08678C88-A544-426A-82A3-64FB157C729A | Total Impressions | 31/01/2016 | 1832 |
80065A54-CBC7-4527-83E2-5A1D0B0B13A0 | 08678C88-A544-426A-82A3-64FB157C729A | Total Impressions | 28/02/2016 | 21339 |
481044CA-F35A-43DD-B34D-402FB0834C52 | 08678C88-A544-426A-82A3-64FB157C729A | Total Impressions | 31/03/2016 | 41460 |
4524C0F1-626F-4EAD-A6A5-E24D8ED7AE27 | 08678C88-A544-426A-82A3-64FB157C729A | Total Impressions | 27/04/2016 | 56704 |
0E192A41-070F-4EC7-8531-371C9703821B | 0A6FCB8A-CB59-4439-8695-A9E9B0DA969E | Total Impressions | 31/12/2015 | 5232 |
79EA47AC-529A-4DF5-B2A5-DBADB7772A75 | 0A6FCB8A-CB59-4439-8695-A9E9B0DA969E | Total Impressions | 08/01/2016 | 5240 |
C25A300D-D641-4A3D-9575-ACBBF0363574 | 0EB4FE2D-1250-48AF-8353-D4EB12071C06 | Total Impressions | 22/12/2015 | 89021 |
751C4D82-9E63-4234-9C7A-DFF78A1EECED | 0EB4FE2D-1250-48AF-8353-D4EB12071C06 | Total Impressions | 31/01/2016 | 90583 |
666673B9-58DC-4B04-8072-7D7A1AF882FD | 0EB4FE2D-1250-48AF-8353-D4EB12071C06 | Total Impressions | 29/02/2016 | 92164 |
38D02F4D-6F88-4CC6-899F-9B50971FB46F | 0EB4FE2D-1250-48AF-8353-D4EB12071C06 | Total Impressions | 31/03/2016 | 93544 |
FD5E6D83-2B84-4D07-8FA7-081A3511BFAB | 0EB4FE2D-1250-48AF-8353-D4EB12071C06 | Total Impressions | 29/04/2016 | 94788 |
I need to compare the Volumes over months (so the day of the month could depend on the readings received)
For example:
Date: 29/04/16 Read: 94788
Date: 31/03/16 Read: 93544
So April Volume is therefor 1,244
I was trying the below but I believe it's not working because the SQL database will just look at Row number instead, there is no order to how the data is populated.
if(Previous([Volume Type]) = [Volume Type] AND
Previous([Serial Number]) = [Serial Number] AND
Previous(Date([Volume Read Date]))<Date([Volume Read Date]) ,
[Volume Count] - Previous([Volume Count])) as Volume,
When I import the data I Order it by AssetId, VolumeType and VolumeReadDate asc;
Any suggestions would be appreciated!
Cheers
Craig
Found the solution was to utilise the Above Function.
Volume - above(TOTAL sum(Volume))
This has fixed all the problems 🙂
Your statement "When I import the data I Order it by AssetId, VolumeType and VolumeReadDate asc;"
Does this mean that you are ordering it in the load statement, or is this your Order By clause in the SQL query (from your database)?
Found the solution was to utilise the Above Function.
Volume - above(TOTAL sum(Volume))
This has fixed all the problems 🙂