Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
sosl11
Contributor III
Contributor III
‎2025-02-14 08:01 AM

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

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?
Labels (1)
Labels
438 Views
1 Solution

Accepted Solutions
sosl11
Contributor III
Contributor III
‎2025-02-17 09:29 AM
Author

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
;

View solution in original post

345 Views
0 Likes
3 Replies
morgankejerhag
Partner - Creator III
Partner - Creator III
‎2025-02-15 03:54 AM

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,
    1,
        0
    ) 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;
403 Views
sosl11
Contributor III
Contributor III
‎2025-02-17 03:40 AM
Author

In response to morgankejerhag

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!

363 Views
0 Likes
sosl11
Contributor III
Contributor III
‎2025-02-17 09:29 AM
Author

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
;
346 Views
0 Likes