calculating Count at the backend

[hitha1512]

Hi Team,

I have a table "Ticket" which holds ticket details and the respective dates.

how do I code the below at backend  for count of tickets falling between the below ranges ?

Min Count of Tickets	Max Count of Tickets	Amount to be charged in dollars
0	150	50
151	250	100
251	400	150
401	550	200
551	700	250
701	850	300
851	1000	350
1001	1250	400
1251	1500	450
1501	1750	500
1751	2000	550
	for every additonal range of 250 tickets	90

Example : If the total count of ticket per day/month/year falls between range 0-150 then price charged would be 50$

Regards,

Hitha Dhani

[BenjaminT]

Hi,

Just to make sure I've understood correctly, do you want to get a Ticket Count by date from your Ticket table, and then you want to use the table above to map each date to the appropriate $ charge?

Thanks

Ben

[hitha1512]

Hi @BenjaminT ,

yes, that's right. 

mainly the part where addition of every 250 tickets is charged 90$. not sure on how to dynamically put in this logic.

[NitinK7]

hello,

if possible then can you please explain using one of the example what you want and share sample data with date filed then that would be helpful.

[hitha1512]

I will have 2 files, one holding Ticket information and the other holding the above mapping table. Attaching both files. Based on the count of tickets from Ticket table I need to calculate the cost using the mapping/reference table.

Assuming the count(Ticket Number) from Ticket are as below, the cost needs to be calculated.

April 2021  - 1021

May 2021 - 1499

June 2021 - 2229

July 2021 - 2445

Output :

the count of tickets for April falls in the range 1001 - 1250, hence the cost should be 400$

the count of tickets for May falls in the range 1251 - 1500, hence the cost should be 450$

the count of tickets for June is above 2000, hence the cost should be 550 + 90 $ ( addition of every 250 ticket range to be charge with 90$ above 2000 count)

the count of tickets for July is above 2445, hence the cost should be 550 + 90 + 90$

Note : The sample data attached is only for reference and will not match the above counts.

[NitinK7]

Hi,

please see attached QVF file and my data

might be this will help

[hitha1512]

Hi Nitin, 

I am unable to open the app. will you be able to post the code here please.

[NitinK7 · Accepted solution]

TableB:
LOAD
"Min Count",
"Max Count",
Amount
FROM [lib://Desktop/Reference.xlsx]
(ooxml, embedded labels, table is Sheet1);

TableA:
LOAD
"Date",
"Ticket Number"
FROM [lib://Desktop/Ticket2.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(TableA)
interval_table:
IntervalMatch("Ticket Number")
Load
"Min Count" ,
"Max Count" as Max_Count
Resident TableB;

Join(TableB)
Load
*
Resident TableA;

Drop Table TableA;

Final:
Load
"Date",
"Ticket Number",
"Min Count",
"Max Count",
if("Ticket Number">="Min Count" and "Ticket Number"<="Max Count",Amount,
if("Ticket Number">2000,peek('Amount',-1,'TableB')+ (ceil(("Ticket Number"-2000)/250)*90) )) as Amount
Resident TableB;
Drop Table TableB;

[hitha1512]

Thank  you. That worked.