topic Re: Display a column based on the count of values in another column in App Development

Display a column based on the count of values in another column

Qlik_Learner11 — Fri, 02 Aug 2024 17:59:36 GMT

Hello Experts,

I need your help on my problem. So, I can't actually make any changes to the script. I have below data in the loaded table.

tkt_nbr	show_id	cust_id
1001	s1	c1
1001	s1	c2
1001	s2	c1
1001	s2	c2
1001	s2	c3
1002	s1	c9
1002	s1	c8
1002	s1	c10
1003	s7	c9
1004	s1	c5
1005	s8	c1

I need to display the ticket numbers which have more than 1 shows or more than 1 customers on it. I need to create a table box or Straight table to achieve this in the app AccessPoint. My understanding is that table box doesn't support calculated dimensions, so my option is to go with Straight table. I need the below output.

tkt_nbr	#shows	#cust
1001	2	3
1002	1	3

Could someone please help? Thank you so much in advance.

Re: Display a column based on the count of values in another column

anat — Fri, 02 Aug 2024 19:13:38 GMT

using load script:

T:
load * where [#shows]+[#cust]>2;
load tkt_nbr,count(DISTINCT show_id) as [#shows],count(DISTINCT cust_id) as [#cust]

Group by tkt_nbr;
;
load * Inline [
tkt_nbr show_id cust_id
1001 s1 c1
1001 s1 c2
1001 s2 c1
1001 s2 c2
1001 s2 c3
1002 s1 c9
1002 s1 c8
1002 s1 c10
1003 s7 c9
1004 s1 c5
1005 s8 c1

](delimiter is ' ')

or else use below logic in frontend:

Re: Display a column based on the count of values in another column

Kushal_Chawda — Mon, 05 Aug 2024 10:51:24 GMT

@Qlik_Learner11 Use below expressions

#shows

=Count({<tkt_nbr={"=count(distinct show_id)>1 or count(distinct cust_id)>1"}>} distinct show_id)

#cust

=Count({<tkt_nbr={"=count(distinct show_id)>1 or count(distinct cust_id)>1"}>} distinct cust_id)

Re: Display a column based on the count of values in another column

Qlik_Learner11 — Mon, 05 Aug 2024 15:12:43 GMT

Thanks so much!

Re: Display a column based on the count of values in another column

Qlik_Learner11 — Mon, 05 Aug 2024 15:12:57 GMT

Thanks so much!