# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## Need help with counts in straight table

Sample data:

 Patient ID City State 101 ABC State 1 101 XYZ State 2 103 XYZ State 2 103 BCD State 1 104 BCD State 1 105 BCD State 1 106 ABC State 1 107 XYZ State 2 108 ABC State 1 109 ABC State 1 110 ABC State 1 108 ABC State 1 106 ABC State 1 105 XYZ State 2 103 XYZ State 2 101 ABC State 1 109 ABC State 1 106 XYZ State 2 102 ABC State 1 105 BCD State 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:

 City Total Patients in City State Total patients in State corresponding to city Total patients in other cities of State ABC 10 State 1 14 4 BCD 4 State 1 14 10 XYZ 6 State 2 6 0

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

Labels (3)

• ### straight table

1 Solution

Accepted Solutions
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:

12 Replies
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?

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

## 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:

Contributor

## Re: Need help with counts in straight table

Thanks for the reply. I got a solution.

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)
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:
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

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