topic Re: Get 7 days data from the start date then again next 7 days in Talend Studio

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

Anonymous — Wed, 24 Jul 2019 11:19:01 GMT

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

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

vapukov — Wed, 24 Jul 2019 12:13:28 GMT

Hi,

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

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

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

Anonymous — Wed, 24 Jul 2019 12:30:57 GMT

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

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

vapukov — Wed, 24 Jul 2019 12:35:19 GMT

so, when 7 days from 20th finished - 27th?

20/07-27/07

28/07-02/08

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

31,03,04,05,06,11,15

what is your source?

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

Anonymous — Wed, 24 Jul 2019 12:43:34 GMT

hello sir,

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

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

thanks

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

lennelei — Wed, 24 Jul 2019 12:47:58 GMT

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?

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

Anonymous — Wed, 24 Jul 2019 12:57:18 GMT

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.

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

vapukov — Wed, 24 Jul 2019 12:59:41 GMT

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

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

Anonymous — Wed, 24 Jul 2019 13:28:44 GMT

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,

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

vapukov — Wed, 24 Jul 2019 13:34:17 GMT

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

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

Anonymous — Wed, 24 Jul 2019 13:46:03 GMT

is it possible to work on datetime, because in table column every datetime having different vaules and i want to calculate sum total values on every day data then next day then up to 7 days, like this:-

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,

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

vapukov — Wed, 24 Jul 2019 13:55:20 GMT

all possible!

but all depends - from what you really want/need ?! 🙂

as variants:

why not make group by day in the database? and retrieve to talend date(without time) and sum per day? any database could do this fast and easy

then you return to original question

alternatives - make 2 tAggregateRow - first for aggregate per day (you will need additional column - datetime truncated to date), then again return to the original task

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

Anonymous — Thu, 25 Jul 2019 13:43:29 GMT

hello @vapukov sir,

can you plz explain me the marked step, after sync columns in tjava row, what code i write for no.3 step.

thanks,

regards,

Manish