Union of two views count distinct patient ID

Brian Smith shared this question 8 months ago
Completed

This is easily done using a custom query but I am trying to use tables for better performance (hopefully). First view has patientID and date and same with second view. when I put them together I get duplicate years and distinct count for each year.

Example:

2022 5,280

2022 38,555

I need a distinct count of the patientID for the whole year between both views for 2022.

The only way that I can think of that I have not tried is creating a third NULL view using that as the first view and join the two other views upon it. But They have to be aggregated as I have 200,000+ records for patientIDs and would like to run for 3 years prior to current year to current YTD.

Any ideas are welcome.

Brian Smith

Replies (15)

photo
1

Hi Brian,

Thanks for reaching out to Yellowfin support.

Could you please let us know the exact version and build of Yellowfin you are currently using it would be great if you can provide the info.jsp page?

It would be great if you can share the screen recording of the process you are doing and upload it into our ftp server?

https://ftp.yellowfin.bi/files/

Regards,

Sri Vamsi

photo
1

Sri,

I have installed some recording software and will update this ticket tomorrow when I get some free time.

Thank you for the quick response.

Brian

photo
1

Hi Brian,

Thanks for your quick response.

Sure, I'm looking forward to get the details and please share the info.jsp file as well along with the recorded video.

Regards,

Sri Vamsi

photo
photo
1

Hi Brian,

Greetings for the Day!

We would like to check, with you if you had the opportunity to read our last response.

Request, you to review and let us know if you have any questions or further assistance is required.

Regards

Sri Vamsi

photo
1

Sri,

Before I upload items I have talked to our team here and we see no way to be able to do a distinct count from two data sources utilizing 2 views in a union in yellowfin as it aggregates distinct counts for each view before applying the union.

So if I could get this question asked: Is it possible to do a distinct count using a union from 2 different data sources (2 views)?

If this is possible I will gladly upload the required items/files.

Thanks for your assistance.

--Brian

photo
1

Hi Brian,

Thanks for your response.

Please allow me sometime, I'll check with our wider team and get back to you as soon as possible.

Regards,

Sri Vamsi

photo
1

Hi Brian,

Greetings of the Day!

Could you please let me know your available time to connect with you and discuss on this issue further. I'll be available in between Mon-Fri 9 PM IST to 6AM IST.

Regards,

Sri Vamsi

photo
1

Sri,

Tomorrow, Friday June 16th, 11 AM CST which is 9PM IST if I did my math right, I will be available.

Thank you,

Brian Smith

photo
1

Hi Brain,

Thanks for your response.

We can connect on the meeting link that I have shared in previous ticket(27754) and we can discuss on both the issues.

Teams Meeting

Regards,

Sri Vamsi

photo
1

Sri,

I am shorthanded again today. Monday or Tuesday will work for me. Let me know.

Thanks, have a great weekend.

Brian

photo
1

HI Brian,

Sure, we can schedule this meeting on Monday 11 AM CST which is 9PM IST. Please join the link below.

Teams Meeting

Regards,

Sri Vamsi

photo
1

Hi Brian,

Could you please join the call? Let me know if you want me to reschedule this meeting.

Regards,

Sri Vamsi

photo
1

Hi Brian,

I have raised a Jira task to our product team on behalf of you. I'll let you know once I received an updated from them.

Regards,

Sri Vamsi

photo
1

Hi Brian,

I hope you are doing well.

We have some feedback on this case from our development team and wanted to clarify a few things, as below.

  1. The first view has patientID and date, and the same with the second view. We wanted to know if these two different views were created from different data sources?
  2. If the views are created from the same database, the client could create a view using freehand SQL, which will make the distinct count. It would help to know how the views are created and how many data sources the client is using.
  3. Also, would that be a fair ask if could provide the "custom SQL" that you are using to achieve the desired outcome?
  4. Can you also share sample data of the views that you have in Yellowfin and the required output so that we can try them in our instances?
  5. Also, are you specifically talking about database views? or YF views? If YF views, are you trying to use the union query in the report (advanced union query) or is it something else?

Regards

Siddhartha

photo
1

Hello Brian,

Greetings of the Day!
We would like to check, with you if you had the opportunity to read our last response.

Request, you to review and let us know if you have any questions or further assistance is required.

Regards

Sri Vamsi

photo
1

Hello Brian,

This is a follow up reminder, that there has been no activity on the ticket in the last 2 days.

As no activity, it usually means either that you have found the solution for the reported issue or that the matter is no longer relevant.

Without any activity, we assume that the ticket can be closed within 5 days (2 days from now).

If this is not the case, please do let us know how we can be of further assistance.

We would be glad/pleased to assist you.

Regards


Sri Vamsi

photo
1

Hello Brian,


This email is to notify you that, you have identified the solution for the reported issue or that this ticket can be resolved for now.

As we find no activity, we are going ahead and mark this ticket as Completed. However, if you ever wanted to revisit this or have anything else we could help you with, please let me know.

We would be glad/pleased to assist you.

Regards

Sri Vamsi

Leave a Comment
 
Attach a file