Calculate number of days, day of week

[kapoorp]

Hi,

I have the data which has information as mentioned below. I need to find out the total days of travel for a person and the day of travel - Monday =1, Tuesday=2 ... Sunday=7 as given in Output. Is any help possible?

i/p:

Name	Date	Amt
A	4/10/2019	190
A	4/11/2019	200
A	6/15/2019	195
A	6/16/2019	200
B	7/2/2019	250
B	7/3/2019	295
C	5/15/5019	2000

O/P:

Name	Days Of Trip	Amt
A	2	390
A	2	395
A	1	200
B	2	445
C	1	2000

Name	Days of Travel
A	1,2
A	3,4
A	4
B	5,6
C	1

(1,2 means the travel ws on Monday and Tuesday. 3,4 means travel was on Wednesday & Thursday etc).

[JosephSerpis]

Hi @kapoorp I mocked a workflow that I think produces the output you describe. I had to change the third table in order to make the workflow work in order to get the summary of the amt column to match your output table based on the mock date from your input table. Let me know what you think?

[kapoorp]

Hi,

You have taken the output table as one of the inputs.  There is only 1 input which is:

 

And in output, the requirement is to have count days travelled and the day of the week on which the user travelled.

[PhilipMannering]

Does this get what you need?

I assume consecutive days that have the same name are considered as a single trip.

P

[kapoorp]

Hi,

This fails when there are 3 or more consecutive days

Name	Date	Amt
A	2019-04-10	200
A	2019-04-11	200
A	2019-05-16	200
A	2019-05-17	200
A	2019-05-18	200

OUTPUT SHOULD BE:

Name	Consecutive Days	Tot Amt
A	2	400
A	3	600

output with your workflow:

Name	Consecutive Days	Tot Amt
A	2	400
A	2	400
A	1	200

[PhilipMannering · Accepted solution]

I just tried changing so there's three consecutive days in a row and seems to work fine.

Take a look at the attached (thought it's pretty much the same).

If you're still having issues then post the workflow where this is failing and I'm sure we can fix it.

P

[kapoorp]

Thanks.