How to ignore hidden fields in group by?

Machiel Treffers shared this question 3 months ago
Answered

Hi all,

At the moment I am trying to build a report containing one sub query. Let's say the main query contains a metric "Qty. items received" and the sub query contains a metric "Qty. items put on stock". I'd like to sum both metrics per hour, because I am interested in how many of the items I have received and how many have already put on stock.

Because I am only interested in items received in a specific way, I added a filter to my Master query to just get those items. I also left outer joined both queries on "Item code".

To achieve this, I add "Item code" to both queries and to the join condition. After that I hide both columns "Item code" as I do not want to display this field. The problem which arises here, is that "Item code" is now added to the GROUP BY clause of the query, and my report is no longer displaying a sum of "Items received" and "Items sold" per hour, but per hour and item code... which is not what I am looking for.

Is there a way to have a join between two sub queries, of which the columns (that are part of the join condition) are ignored in the group by?

Thanks!

Best regards,

Machiel

Comments (4)

photo
1

Hi Machiel,


Thanks for your message.


It's a little difficult to understand your question without a little more information about the Report. If possible, could you send through some annotated screenshots or a short video describing what you are trying to achieve?


One thing is that I feel like you might benefit from using a dummy variable, the reason is that the subquery is a subset of the result of the Master query. This will filter the results based on the filter values you have set in the Master query. A way around this is to set a constant 'dummy' variable as a Calculated Field, where you can then join different subqueries.


/deec393fd0e65dba9b7bde4cd2af94af


/39324fc9d6fe8c154668c2297076ac28


In the Report below, I have joined two subqueries to the Master query. One where the join is on the Dummy variable (set to the value 1) and the second where the join is on the Athlete Region column.


This results in the first subquery aggregating by the total number of Female Athletes grouped by the Dummy variable (therefore the entire dataset) and the second subquery aggregates the total number of Male Athletes group by the Athlete Region.


/5e130cab5e5b914de0431857de59f8b7


Hopefully, this provides a little direction with your case. Feel free to send through some screenshots of your Report. If they contain any sensitive information, let me know and I can move this to a private ticket.


Kind regards,

Simon

photo
1

Hi Machiel,


I hope you've been well.


I just wanted to check in to see how you are traveling with this question.


Let me know if there is anything else that I can help to clarify, and I will get back to you shortly.


Kind regards,

Simon

photo
1

Hi Simon,

Sorry for my late reply. Thank you for your suggestion. I managed to solve my issue in the Yellowfin view, but I will also have a look at your proposed solution to see if it can help us out with future cases.

Kind regards,

Machiel

photo
1

Hi Machiel,


Not a problem, I'm glad I could help and I'm glad to hear you've managed to find a solution.


I will go ahead and close this question, please feel free to re-open it at a later date if you have any follow-up questions.


Kind regards,

Simon