advanced set analysis?

Thu, 11 Feb 2016 00:07:31 GMT

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.

Re: advanced set analysis?

sunny_talwar — Thu, 11 Feb 2016 09:52:55 GMT

I might not have full understood your question, so bear with me if you think I am talking total nonsense but...

I am just curious about why you think that we cannot accomplish scenario using Pivot Table. Seems like a good candidate for pivot table where you can add conditions in your expression's set analysis with patient number and sequence as your dimension and sequence is pivoted.

And even the second scenario can be programmed on the front end (or partially script and partially UI based) using if statements.

Best,

Sunny

topic advanced set analysis? in App Development

advanced set analysis?

Re: advanced set analysis?