topic Re: Mark rows total Amount per TicketID greater than 700 on two consecutive dates in App Development

Mark rows total Amount per TicketID greater than 700 on two consecutive dates

sosl11 — Fri, 14 Feb 2025 15:14:09 GMT

Hi,

tempTickets:
LOAD * INLINE [
Year, TicketID, ProductID, CityID, Date, Amount
2025, 70009, 30, 882, 10/01/2025, 400
2025, 70009, 30, 919, 11/01/2025, 200
2025, 70009, 32, 672, 12/01/2025, 400
2025, 70009, 30, 882, 13/01/2025, 200
2025, 80009, 30, 126, 17/01/2025, 300
2025, 80009, 30, 849, 18/01/2025, 300
2025, 90009, 30, 497, 06/01/2025, 400
2025, 90009, 31, 497, 07/01/2025, 450
2025, 90009, 30, 661, 08/01/2025, 200
2025, 15917, 31, 661, 08/01/2025, 200
2025, 15917, 30, 497, 09/01/2025, 200
2025, 13040, 30, 661, 26/01/2025, 400
2025, 13040, 30, 497, 28/01/2025, 450
2025, 93000, 32, 497, 02/02/2025, 500
2025, 93000, 32, 661, 04/02/2025, 500
2025, 13040, 30, 661, 26/01/2025, 300
2025, 13040, 30, 661, 27/01/2025, 300
2025, 93099, 32, 497, 02/02/2025, 200
2025, 93099, 32, 497, 03/02/2025, 650
2025, 90099, 30, 497, 06/01/2025, 200
2025, 90099, 30, 497, 08/01/2025, 800
];

I want to give all the rows the Total of the Amount per TicketID is greater than 700 on two consecutive dates a '1' and the rest of the rows a '0'. How can I achieve this in script?

Result would be like this:

Year, TicketID, ProductID, CityID, Date, Amount, Greater700
2025, 70009, 30, 882, 10/01/2025, 400, 1
2025, 70009, 30, 919, 11/01/2025, 200, 1
2025, 70009, 32, 672, 12/01/2025, 400, 1
2025, 70009, 30, 882, 13/01/2025, 200, 1
2025, 80009, 30, 126, 17/01/2025, 300, 0
2025, 80009, 30, 849, 18/01/2025, 300, 0
2025, 90009, 30, 497, 06/01/2025, 400, 1
2025, 90009, 31, 497, 07/01/2025, 450, 1
2025, 90009, 30, 661, 08/01/2025, 200, 1
2025, 15917, 31, 661, 08/01/2025, 200, 0
2025, 15917, 30, 497, 09/01/2025, 200, 0
2025, 13040, 30, 661, 26/01/2025, 400, 0
2025, 13040, 30, 497, 28/01/2025, 450, 0
2025, 93000, 32, 497, 02/02/2025, 500, 0
2025, 93000, 32, 661, 04/02/2025, 500, 0
2025, 13040, 30, 661, 26/01/2025, 300, 0
2025, 13040, 30, 661, 27/01/2025, 300, 0
2025, 93099, 32, 497, 02/02/2025, 200, 1
2025, 93099, 32, 497, 03/02/2025, 650, 1
2025, 90099, 30, 497, 06/01/2025, 200, 0
2025, 90099, 30, 497, 08/01/2025, 800, 0
];

Or possible with Set Expression?

Re: Mark rows total Amount per TicketID greater than 700 on two consecutive dates

morgankejerhag — Sat, 15 Feb 2025 09:00:44 GMT

This is best done in the script:

tempTickets:

LOAD

Year,

TicketID,

ProductID,

CityID,

date#(Date,'DD/MM/YYYY') as Date,

Amount

INLINE [

Year, TicketID, ProductID, CityID, Date, Amount

2025, 70009, 30, 882, 10/01/2025, 400

2025, 70009, 30, 919, 11/01/2025, 200

2025, 70009, 32, 672, 12/01/2025, 400

2025, 70009, 30, 882, 13/01/2025, 200

2025, 80009, 30, 126, 17/01/2025, 300

2025, 80009, 30, 849, 18/01/2025, 300

2025, 90009, 30, 497, 06/01/2025, 400

2025, 90009, 31, 497, 07/01/2025, 450

2025, 90009, 30, 661, 08/01/2025, 200

2025, 15917, 31, 661, 08/01/2025, 200

2025, 15917, 30, 497, 09/01/2025, 200

2025, 13040, 30, 661, 26/01/2025, 400

2025, 13040, 30, 497, 28/01/2025, 450

2025, 93000, 32, 497, 02/02/2025, 500

2025, 93000, 32, 661, 04/02/2025, 500

2025, 13040, 30, 661, 26/01/2025, 300

2025, 13040, 30, 661, 27/01/2025, 300

2025, 93099, 32, 497, 02/02/2025, 200

2025, 93099, 32, 497, 03/02/2025, 650

2025, 90099, 30, 497, 06/01/2025, 200

2025, 90099, 30, 497, 08/01/2025, 800

];

left join (tempTickets)

Load

TicketID,

sum(Amount) as TotalAmount

resident tempTickets group by TicketID;

Flags:

Load

if(peek(TicketID)=TicketID and peek(Date)=(Date-1) and TotalAmount>700,

) as Flag

resident tempTickets order by TicketID,Date asc;

left join (tempTickets)

Load

TicketID,

max(Flag) as Greater700

resident Flags group by TicketID;

drop table Flags;

Re: Mark rows total Amount per TicketID greater than 700 on two consecutive dates

sosl11 — Mon, 17 Feb 2025 08:40:35 GMT

Hey,

Thanks for the reply, it almost works as desired as the totalamount should only be based on two consecutive days as well not over all dates. For example when we add this line to the Inline Table:

2025, 13040, 30, 661, 03/02/2025, 350

It will show a Totalamount of 1800 for TicketID 13040 where it actually is supposed to show only the dates 26/01 + 27/01 with a Totalamount of 1000 and dates 27/01 + 28/01 with 750 as Totalamount for those two consecutive days? Currenly it marks the new row with a Date that is not consecutive with a 1 = Greater700. Is this possible to achieve? Thanks!

Re: Mark rows total Amount per TicketID greater than 700 on two consecutive dates

sosl11 — Mon, 17 Feb 2025 14:58:59 GMT

Figured it out, I think.

tempTickets:

LOAD

Year,

TicketID,

ProductID,

CityID,

date#(Date,'DD/MM/YYYY') as Date,

Amount

INLINE [

Year, TicketID, ProductID, CityID, Date, Amount

2025, 70009, 30, 882, 10/01/2025, 400

2025, 70009, 30, 919, 11/01/2025, 200

2025, 70009, 32, 672, 12/01/2025, 400

2025, 70009, 30, 882, 13/01/2025, 200

2025, 80009, 30, 126, 17/01/2025, 300

2025, 80009, 30, 849, 18/01/2025, 300

2025, 90009, 30, 497, 06/01/2025, 400

2025, 90009, 31, 497, 07/01/2025, 450

2025, 90009, 30, 661, 08/01/2025, 200

2025, 15917, 31, 661, 08/01/2025, 200

2025, 15917, 30, 497, 09/01/2025, 200

2025, 13040, 30, 661, 26/01/2025, 400

2025, 13040, 30, 497, 28/01/2025, 450

2025, 93000, 32, 497, 02/02/2025, 500

2025, 93000, 32, 661, 04/02/2025, 500

2025, 13040, 30, 661, 26/01/2025, 300

2025, 13040, 30, 661, 27/01/2025, 300

2025, 93099, 32, 497, 02/02/2025, 200

2025, 93099, 32, 497, 03/02/2025, 650

2025, 90099, 30, 497, 06/01/2025, 200

2025, 90099, 30, 497, 08/01/2025, 800

2025, 13040, 30, 661, 03/02/2025, 350

]

where TicketID = 13040

;

Tickets:

NoConcatenate

Load

TicketID,

Date,

Sum(Amount) as Amount

Resident tempTickets

Group By TicketID, [Date];

Drop Table tempTickets;

tempTickets:

NoConcatenate

Load

TicketID & '|' & Num(Date) as Ticket,

TicketID & '|' & Num(Date-1) as TicketMin1

Resident Tickets;

Drop Table Tickets;

left join (tempTickets)

DateTicket:

Load

Ticket,

sum(Amount) as TotalAmountDate

Resident tempTickets

group by Ticket

;

left join (tempTickets)

DateTicketMin1:

Load

TicketMin1 as Ticket,

sum(Amount) as TotalAmountDateMin1

Resident tempTickets

Group By TicketMin1

;

left join (tempTickets)

DateTicket:

Load

If(TotalAmountD>700,1,0) as Greater700,

Load

Ticket,

sum(TotalAmountDate)

+ Sum(TotalAmountDateMin1) as TotalAmountD

Resident tempTickets

Group By Ticket

;