Adding Dimension - Simple Application

[#1]

Hello,

I have a basic data set that has a handful of columns, and values that are given by date in each row. Basically, the data set reads...

Date	Room 1 Patients	Room 1 Images	Room 2 Patients	Room 2 Images
1/1/2016	150	500	200	580
1/2/2016	140	550	150	700
1/3/2016	100	400	120	600

Using the auto-discover feature, the columns headings are all made into dimensions that I can then add filter panes to in the application. I have created some filters by date, which is important to view. However, I also want to create a dimension called "room" that will allow me to see one or the other.

I'm a new user, so if someone could help direct me the most sensible way to do this in Qlik Sense, I'd appreciate it.

I've attached my app, in case anyone wants to take a look at what I've tried thus far.

[Anonymous]

Hi Patrick,

I looked at your dataset in the .qvf and see that you have master calendar table linked with 2016 table. I believe the reason the table 2016 has field names has a room number on it (Room 2 Patients, Images Room 2)  is to classify the patient and image count per room. The nicer way to do this would be to have a field 'Room Number' in one of your table and this field can have values: 1,2,3,4,5. You can then use this field along with the date field in the table as dimensions to show the patient and image count.

[mgranillo]

Patrick,

I agree with Nakul.  You're data structure is not conducive to your needs. 

I would suggest altering your data structure.  Your question made me think of these articles:The Crosstable Load and  The Generic Load.

They might help get you started thinking about transforming your data.

Mike

[#4]

Thanks. Would you suggest adding a derived field for the room? I appreciate the link to the articles on Crossable and Generic Load, but if I pivot the data set, I'm still left a more detailed dataset than I'd like to sort by.

[mgranillo]

Patrick,

I would alter the table to have four columns [Date, Patients, Images, Room].

Depending on the source and quantity of your data, you may/may not want to do the transformation in Qlik.

Mike

[mgranillo]

Here's a sample app with some transformations. 

[#7]

Michael, I don't see an app attached, but I took your advice and reworked the original dataset to include a room number. It was a good lesson that sometimes it's easier to clean the data outside Qlik if you know beforehand what you want to see. Thanks again for the help