Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rvijayanth
Contributor

Need help with counts in straight table

Sample data:

Patient IDCityState
101ABCState 1
101XYZState 2
103XYZState 2
103BCDState 1
104BCDState 1
105BCDState 1
106ABCState 1
107XYZState 2
108ABCState 1
109ABCState 1
110ABCState 1
108ABCState 1
106ABCState 1
105XYZState 2
103XYZState 2
101ABCState 1
109ABCState 1
106XYZState 2
102ABCState 1
105BCDState 1

 

Above shown data is patient ID for three cities:

Cities ABC and BCD belonging to State 1 and city XYZ belonging to State 2. 

ABC city has 10, BCD has 4 and XYZ has 6 patients.

So State 1 has a total of 14 patients and State 2 has total of 6 patients. 

I want ONE straight table like shown below for comparison:

CityTotal Patients in CityStateTotal patients in State corresponding to cityTotal patients in other cities of State
ABC10State 1144
BCD4State 11410
XYZ6State 260

 

I need help with "Total patients in State corresponding to each city" and "Total patients in other cities of State"

1 Solution

Accepted Solutions
Partner
Partner

Re: Need help with counts in straight table

1) Total Patients in City: Count([Patient ID])

2) Total patients in state corresponding to city : count (total < State> [Patient ID])

3) Total patients in other cities of state : count(total <State> [Patient ID])- Count([Patient ID])

Result:

Capture.PNG

12 Replies
tan_chungkam
Contributor

Re: Need help with counts in straight table

For column - Total Patients in City, you can try use this set analysis:

IF(City='ABC', count({<City={'ABC'}>}[Patient ID]),
IF(City='BCD', count({<City={'BCD'}>}[Patient ID]),
IF(City='XYZ', count({<City={'XYZ'}>}[Patient ID]))))

Re: Need help with counts in straight table

Check this?

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Need help with counts in straight table

1) Total Patients in City: Count([Patient ID])

2) Total patients in state corresponding to city : count (total < State> [Patient ID])

3) Total patients in other cities of state : count(total <State> [Patient ID])- Count([Patient ID])

Result:

Capture.PNG

rvijayanth
Contributor

Re: Need help with counts in straight table

Thanks for the reply. I got a solution. 

rvijayanth
Contributor

Re: Need help with counts in straight table

Thanks for the reply. This is exactly what I wanted. Cant believe how simple it is. 

Can you also help me to perform the same in the load script?

My data is too big and I am getting timeout error so I want to calculate all these during load. 

Re: Need help with counts in straight table

@rvijayanth
Not sure, why my answer is not helped for you? I am bit worried about that? You don't have license to open the document? That's fine you found the logic.
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rvijayanth
Contributor

Re: Need help with counts in straight table

I'm using qliksense. Is your attachment a qlikview file?
Thanks for the reply. Can you help me achieve the same in the load script please?

Re: Need help with counts in straight table

I still not happy of your reply, FYI - Qlikview and Qliksense are similar for concepts like set analysis, Data model etc. because QIX is same for both. BTW, Try this for script?

Sample:
LOAD [Patient ID],
City,
State
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Need-help-with-counts-in-straight-table/m-p/1524247#...]
(html, utf8, embedded labels, table is @1);

Join
LOAD City, Count([Patient ID]) as [Total Patients in City] Resident Sample Group By City;

Join
LOAD State, Count([Patient ID]) as [Total patients in State corresponding to city] Resident Sample Group By State;

Final:
NoConcatenate
LOAD *, [Total patients in State corresponding to city]-[Total Patients in City] as [Total patients in other cities of State] Resident Sample;
DROP Table Sample;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Need help with counts in straight table

I assume he does not have Qlikview installed / or does not have a license and thus, he wouldn't be able to open ur qvw to see it