Get 7 days data from the start date then again next 7 days

[Anonymous]

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

[vapukov]

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:

• 7 calendar days (if gaps in sales)
• 7 sequenced dates, even if gap for 20 days it will be count as 2 days

[Anonymous]

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

[vapukov]

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?

[Anonymous]

hello sir,

20,22,23,26,27,28,30

31,03,04,05,06,11,15 this type of dates in my source

thanks

[lennelei]

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?

[Anonymous]

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. 

[vapukov]

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

[Anonymous]

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,

[vapukov]

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