Hello All,
I've been working on trying to delve further into set analysis and looking to replicate some common examples from my work into some dimension / measures etc and having a bit of a hard time with it. I'd be interested in any feedback from you lot and see if you've had any success with similar issues:
scenario 1: pivoting data
Now I'm not talking about a pivot table, but something similar to the what i might do in SQL. I come from the health care industry and a very common request might be to report on the first however many diagnosis codes for a patient. based on the structure of the database i might have something similar to:
patient number | sequence | diagnosis code
123456789 | 1 | 1234
123456789 | 2 | 2345
123456789 | 3 | 3456
now in SQL i might choose to pivot this information like so:
SELECT patient number, [1],[2],[3]
FROM table as source
PIVOT (MAX(Diagnosis code FOR Sequence IN ([1],[2],[3])) as pivot table
Giving you
patient number | [1] | [2] | [3]
123456789 | 1234 | 2345 | 3456
I assume what you are going to say is that it's best to do this sort of transformation in the data load editor. the problem that i have with that is then that prevents you from having a common set of database tables upon which you can build multiple visualizations. why would i want to have to refresh and maintain multiple instances of the same database tables to account for this type of scenario? wouldn't i ideally be able to do this sort of thing via set analysis?
scenario 2: assign a literal based on a case statement
maybe another basic concept. let's say i have a list of facilities and i want to create some sort of dimensional label for a segment of them. in SQL i might do something like:
SELECT
CASE WHEN facility = Hospital X THEN 'Main'
WHEN facility = Hospital Y THEN 'Something'
ELSE 'Other' END AS Label
FROM table
Here i have evaluated a condition and assigned a new value to it based on the result. Again, I am pretty sure you can do this either via the data load editor or by creating a small reference file, but is there some way to define these things through set analysis?
my big concern is, i come from a world where i might have a core set of 9-10 large tables all with millions if not billions of rows of information. does it make sense to have to refresh multiple instances of those tables across multiple dashboard visuals in order to be able to make these types of things happen? I'm hoping that i'm just still inexperienced at set analysis and haven't realized it's full potential.
interested in the communities feedback.
