New Topic

Community Topic Question Report Builder

Nathan Schroeder

NA Business Team

Assigned

Category Report Builder

Product Version 7.1

How to display latest child record with parent from join view?

Mohammad Rehman shared this question ago

Answered

I have a join of table A (Parent) and table B ( child) with one (parent) to many (childs).

Table A columns

PID

Name

Table B Columns

PID

Date&Time

Note

Data Table A

11111 abc

22222 def

33333 xyz

Data Table B

11111 05/01/2017 09:00 AM test1

11111 05/11/2017 12:00PM test2

22222 05/11/2017 11:00AM test#1

22222 05/11/2017 11:30AM test#2

22222 05/11/2017 11:45AM test#3

33333 05/12/2017 07:15AM test first

I want to display the parent with most recent child.

Expected Output:

11111 abc 05/11/2017 12:00PM test2

22222 def 05/11/2017 11:45AM test#3

33333 xyz 05/12/2017 07:15AM test first

I have to achieve this using reporting functions, i have no option to use SQL or such thing.

Thanks in Advance

Mohammad

The same question

Best Answer

Nathan Schroeder ●

Hi Mohammad,

When I said max date I simply meant to use a "Maximum" aggregation on your date field. If you could send me a screenshot of your "data" tab, I should be able to provide more specific suggestions.

a93e118fe2bb4ce605e567be7a3f03bc

5bf478c9e429849dba226f9289df495c

This aggregation makes it so that only the most recent date value is returned, when you join the append sub-query, everything in that append will be specific to the record on that date.

Let me know if this makes more sense!

Regards,

Nathan

Replies (3)

Nathan Schroeder ●

Hi Mohammad,

Do you have a few minutes to screen-share and demonstrate this problem to me? If so, please let me know what time works best for you. I am on United States Mountain Time.

Regards,

Nathan

Reply URL

Mohammad Rehman ●

Hi Nathan,

I always appreciate the level of support you and other respectable members on yellowfin bi community provide. Unfortunately i am working with federal govt and we are not allowed to share so many things including the screen.

I am not sure if it is possible with current versions of yellowfin to display two tables joins 1 - many relation and display the latest entry with parent record just using the reporting function.

Thanks

Mohammad

URL

Nathan Schroeder ●

Hi Mohammad,

I would suggest using an append sub-query to display the child information for example this could look like:

PID, name, child Max(Date)

11111, abc, 05/11/2017 12:00PM

and then create an append sub-query that joins on name=name and max(Date)=date. This would contain the notes for the single row that matches these conditions.

In the example above only one row matches the conditions date=05/11/2017 12:00PM and name=abc, so the sub-query would show "test2"

Please let me know if this makes sense.

Regards,

Nathan

Reply URL

Mohammad Rehman ●

Hi Nathan,

Appreciate your time and help, I don't understand the first part.

How would i use the master query which display this? Date is coming from child not master, PID and Name from master.

PID, name, child Max(Date)

11111, abc, 05/11/2017 12:00PM

Thanks

Mohammad

URL

Mohammad Rehman ●

Hi Nathan,

Sorry not understanding your approach first, I am watching the sub-query video and let me try this first.

Regards

Mohammad

URL

Mohammad Rehman ●

Hi Nathan,

My brain stalled and unable to think how to use MAX(Date) function on runtime report?

Please guide.

Regards

Mohammad

URL

Mohammad Rehman ●

I tried using the calculated field MAX(Date) but it returns null.

URL

Nathan Schroeder ●

Hi Mohammad,

When I said max date I simply meant to use a "Maximum" aggregation on your date field. If you could send me a screenshot of your "data" tab, I should be able to provide more specific suggestions.

a93e118fe2bb4ce605e567be7a3f03bc

5bf478c9e429849dba226f9289df495c

This aggregation makes it so that only the most recent date value is returned, when you join the append sub-query, everything in that append will be specific to the record on that date.

Let me know if this makes more sense!

Regards,

Nathan

URL

Mohammad Rehman ●

I tried to use the calculated field child MAX(EntryID) on master and then append sub-query and it did not work.

When I go back to check calculated field it shows the this message on validation "Subqueries not supported"

URL

Nathan Schroeder ●

Hi Mohammad,

Unfortunately it will be a bit hard for me to tell it is going wrong without some visual help, can you provide a screenshot of what you are trying to do?

Regards,

Nathan

URL

Mohammad Rehman ●

Hi Nathan,

Thanks for explaining in detail, Unfortunately, I am using the ver 7.1 (BMC supported), I don't see MAx and Min aggregate function that is the problem i believe.

Thanks for your help as always.

Regards

Mohammad

URL

Nathan Schroeder ●

Hi Mohammad,

No worries, unfortunately, I am not sure on what Yellowfin functionality BMC does and does not include. If you are interested in perusing this further I would recommend getting in contact with BMC support who should be able to provide you with a more comprehensive answer than I can.

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!

Regards,

Nathan

Reply URL