Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
In my scenario i have a table which can contain 10 k rows data , i want to perform operation to get total of 7 days cost from the start date then again next 7 days data.
input table like this:-
object_id, Object_name, Object_cost, Object_added_date
1,keyboard, 1000,10-10-2016
2,mouse, 1500,11-10-2016
3,monitor, 2000,12-10-2016
4,cellphone,10000,14-10-2016
5,headphone,1000,14-10-2016.
Thanks in advanced
regards,
Manish
Hi,
1.
simplest way - just little adjust the business requirements.
for example if it is a sales, much more simple - count by the calendar week. In this case just need group by week number
2.
for 7 days from the starting date - is also possible, but need to clarify what is 7 days means:
thanks Sir for reply,
in my requirement the 7 days data are having some gaps like the starting date is 20 July the next is 22 July, 23 July..
and plz give more detail about which component are used in this scenario.
thanks Again
regards,
Manish
so, when 7 days from 20th finished - 27th?
20/07-27/07
28/07-02/08
or
20,22,23,26,27,28,30
31,03,04,05,06,11,15
what is your source?
hello sir,
20,22,23,26,27,28,30
31,03,04,05,06,11,15 this type of dates in my source
thanks
What is the expected result? A simple flow with the total for the next 7 days such as this :
start_date | total for next 7 days 20 | 12000 (total for dates 20,22,23,26,27,28,30) 31 | 15300 (total for dates 31,03,04,05,06,11,15)
What do you want to do if 2 rows have the same date?
Do you need to group by object id?
actually i worked on latitude-longitude data, i already done with a job for find distance between lat -long, but in the next scenario i have a source which is having total daily distances between lat-long as datetime wise, so in the next task i want to calculate sum of 7 days of data from start date, and then next 7 days and so on.
1. define global variables (tSetGlobalVariable)
WEEK_NUMBER==1
WEEK_DAY==1
START_DATE==minimum date from the source
2. sort input by date ASCENDING - add ORDER BY date into tDBInput or use tSortRow for csv input
3. use tJavaRow for add 1 more column (in schema) - week_number
for each row:
- compare date from current row with START_DATE, if equal - do nothing, if current date > START_DATE than store date into START_DATE,
- if WEEK_DAY <7 increase WEEK_DAY = WEEK_DAY+1, if WEEK_DAY ==7, reset WEEK_DAY = 1, increase WEEK_NUMBER
- assign column week_number with variable WEEK_NUMBER
4. tAggregateRow - group by WEEK_NUMBER
5. use output component to store result
sir,
is it working on Date Time data like in my source, data is datetime wise like this "2019-02-20 05:43:00","2019-02-20 05:45:00", , it means there is multiple datetime for single day, then as it is all 7 day data having multiple dateTime data ..
source data :-
_added_date |
2019-02-20 16:13:27 |
2019-02-20 16:13:55 |
2019-02-20 16:14:29 |
2019-02-20 16:14:56 |
2019-02-20 16:15:28 |
2019-02-21 03:40:26 |
2019-02-21 03:40:58 |
2019-02-21 03:41:28 |
2019-02-21 03:41:57 |
2019-02-21 03:42:27 |
2019-02-22 03:41:17 |
2019-02-22 03:41:47 |
2019-02-22 03:42:18 |
2019-02-22 03:42:48 |
2019-02-23 05:21:18 |
2019-02-23 05:21:44 |
2019-02-23 05:22:15 |
2019-02-23 05:22:45 |
thanks,
is it working on Date Time data like in my source, data is datetime wise like this "2019-02-20 05:43:00","2019-02-20 05:45:00", , it means there is multiple datetime for single day, then as it is all 7 day data having multiple dateTime data ..
not, but you can always truncate DateTime to date, just one more step