How to left join on pivot table

[tomer1]

Hi
I am using pivot table to show the amount of total diagnoses (to be referred as value) per category (source) per clinic.
The clinic is for the user to choose from a dropdown.
The pivot includes the value per source.

I need the pivot table to include all sources that are available in the table, not just the ones which the specific clinic has. The purpose is to show sources with zero values.

For example (see attached figure) there should be additional sources with zeros as values.

Do you have any work around to accomplish this in Quicksight?

Thanks!

[Brett]

Hi @tomer1,
So if I understand correctly, you’d like to create a static field that lists all sources, so even if a specific location/clinic does not have that source, it will still be on the list?

There have been a couple other recent posts that were similar to this request, take a look at the posts below and let me know if one works as a solution for your case:

Static Table for Visualization

Hi @Giridhar.Prabhu, Transforming the Booked Channel field into a new field that only shows individual channels isn’t exactly possible but a work-around you could use is to setup a denseRank calculation, partitioned by channel, and filter down to 1 row per channel type (just rank 1s). Then you could use LAC-W calculations, like sumOver, to retrieve your values for the Booked and Reported fields. Once again partitioning those fields by the booked channel field to ensure they are grouped appropr…

How can I build a minimum and maximum range table in quicksight

Hi @jcolucci It appears your {Unit Value Amount_USD} is using an aggregation function (SUM ?). Looking at your requirement it seems you could put your rows at the lowest level into these different brackets and your calculated field in that can be just the value at the lowest level. Alternatively, you may need to use Level Aware Calculations to get the computations return a value at the row level. See a similar issue resolved here. Regards, Giri

[tomer1]

I read to attached posts and not sure they are relevant.
I’ll try to better reflect the issue here:

Clinic is associated to EHR (could be one of 4 options).
Each EHR has it’s own set of relevant sources. For example, in Athena EHR, the relevant sources are: Encounter, Inferred, Problem, Document, Claims.
In the attached figure is example of clinic that lack some of the sources.

How can I make sure to show even the sources with value of 0?

[lary_andr]

@tomer1 I hope this message finds you well.

Regarding your issue, I have a question: Are you sure that your dataset includes null or zero data for each category? It seems to me that you need to verify whether the information, even if it’s zero, is present in your dataset for each category you wish to display.

Please let me know if this helps.

[tomer1]

The dataset doesn’t include nulls for each category. This is why I wish to make a left join here.
Is there a way to create a calculated field to show all relevant sources per EHR?
And then create another calculated field that calculates the value per source?

[tomer1]

And also worth to mention - I want to use a filter in the pivot table to show only 1 clinic at a time from a dropdown

[tomer1]

Example data

EHR, clinic, patient_id, source, hcc
Athena, Clinic A, 1, Inferred, 108
Athena, Clinic A, 2, Encounter, 108
Athena, Clinic B, 3, Encounter, 17
Athena, Clinic B, 4, Document, 17
Athena, Clinic C, 5, Problem, 10

In this example, when choosing from dropdown any Athena clinic, I wish to see all relevant sources - in this example 4 sources: Inferred, Encounter, Document, Problem.

The challenge is to make a pivot table that will include all sources per EHR (in this example - 4 sources), then add another calculated field with the value per source. If the source doesn’t exist for the chosen clinic, then show 0.

Hope it makes more sense now.
Thanks!

[lary_andr]

Hi @tomer1 now I understand your problem!

There is some suggestions for make it work:
You can perform a left join in quicksight(qs) using the Data Prep interface. However, for complex or large datasets, it’s often more efficient to handle joins in a separate data preparation tool or during the ETL process. But if you really want to do this in qs:

1. Reference Table: create a reference table with all possible combinations of EHR, clinic, and sources.
2. Left Join: In qs Data Prep, import your original data and the reference table. Use the “Join” feature to perform a left join.
3. Calculated Fields: Use ifelse(isNull({value}), 0, {value}) to replace nulls with zero.
4. Pivot Table Setup: Configure your pivot table to display the joined dataset, ensuring it shows null or zero values.
5. Clinic Filter: Add a filter for clinic selection.

tell me if it’s work,please!!

[Brett]

Hi @tomer1,
It’s been awhile since we last heard from you, did you the solution provided above work for your case or did you have additional questions?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

[Brett]

Hi @tomer1,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!