topic Re: Countifs based on another field in QlikView

Countifs based on another field

tinapullen1 — Sat, 16 Nov 2024 02:10:30 GMT

Hi, could someone please help me with a countifs query that I have: I'm trying to show total number if the Trip # and the Customer # are the same e.g. Customer 1 had 3 orders on trip 1 so the result number I would be looking for is 3. Customer 2 had 2 orders on trip 1 so the result would be 2.

Customer #	Trip #	Result
Customer 1	1	3
Customer 1	1	3
Customer 1	1	3
Customer 2	1	2
Customer 2	1	2
Customer 3	2	1
Customer 4	2	1

Re: Countifs based on another field

sunny_talwar — Mon, 21 Oct 2019 12:08:29 GMT

Add Customer # and Trip # as dimension and use Count([Trip #]) as your expression

Re: Countifs based on another field

tinapullen1 — Mon, 21 Oct 2019 13:54:12 GMT

Thank you for coming back to me Sunny - I should have been more clear. I'm looking to do it in the script?

Re: Countifs based on another field

sunny_talwar — Mon, 21 Oct 2019 14:17:10 GMT

Left Join the Count like this may be

Left Join (TableName) LOAD [Customer #], [Trip #], Count([Trip #]) as Result From TableName Group By [Customer #], [Trip #];

Re: Countifs based on another field

Brett_Bleess — Wed, 30 Oct 2019 16:20:48 GMT

Hey Tina, did Sunny's last post get you what you needed for your use case? If so, do not forget to come back to the post and use the Accept as Solution button on Sunny's post to give him credit and let others know the guidance helped you get things working. If you are still working on things, leave an update on where things stand.

Regards,
Brett