Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hitha1512
Creator
Creator

calculating Count at the backend

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

1 Solution

Accepted Solutions
NitinK7
Specialist
Specialist

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;

 

NitinK7_0-1636448667053.png

 

View solution in original post

8 Replies
BenjaminT
Partner - Creator
Partner - Creator

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
Creator
Creator
Author

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
Specialist
Specialist

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
Creator
Creator
Author

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
Specialist
Specialist

Hi,

please see attached QVF file and my data

might be this will help

hitha1512
Creator
Creator
Author

Hi Nitin, 

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

NitinK7
Specialist
Specialist

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;

 

NitinK7_0-1636448667053.png

 

hitha1512
Creator
Creator
Author

Thank  you. That worked.