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

Mohammad Rehman shared this question 3 years 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

Best Answer
photo

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

Comments (3)

photo
2

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

photo
1

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

photo
photo
2

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

photo
1

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

photo
1

Hi Nathan,


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

Regards

Mohammad

photo
1

Hi Nathan,


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

Please guide.


Regards

Mohammad

photo
1

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

photo
2

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

photo
1

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"

photo
1

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

photo
1

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

photo
photo
1

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