Merge results from queries on 2 universes

[former_member1136323]

Hello,

I would be very grateful for any help with my attempts to merge two queries from separate universes (SAP Web Intelligence Ver 4.2).

The “Incidents” Universe holds transport services affected by incidents as summarised in the following table:

The “Timings” Universe holds the actual timings for each service along their particular route. Each service runs once per calendar day.

(the above table excludes the complete SN1, SN2, SN3 and SN4 timings on 02/01/2019)

I would like to join the two queries into a single report as follows:

My attempt so far has used the following steps:

• 1. Query 1 extracts the incidents and affected services from the “Incidents” Universe.
• 2. Query 2 extracts the timings from the “Timings” Universe. Query 2 includes the following filters:
• a.[Date of Service] In List of Results from another query [Query 1].[Date of Service]
• b.[Service Name] In List of Results from another query [Query 1].[Service Affected]
• Note that Query 2 will extract timings for all services on both days.
• 3. In the Report “Available Objects”, I have created the following Merged Dimensions:
• a.Service Affected ([Query 1].[Service Affected] with [Query 2].[Service Name])
• b.Date of Service ([Query 1].[Date of Service] with [Query 2].[Date of Service])

If I create the first two columns of the report using:

• Merged Dimensions > [Service Affected] > [Service Affected] (Query 1)
• Merged Dimensions > [Date of Service] > [Date of Service] (Query 1)

then the list of Service/Date combinations looks correct – i.e. I am using the services/dates which have been affected by incidents.

In order to add the remaining columns, I am led to believe that these should be “detail” objects. If I create a detIncidentNumber “detail” object for [Incident Number] (which uses the merged [Date of Service] dimension as the “associated dimension”) and then add this column, the resulting table appears to include ALL services from Query 2 – but I only want to keep those services which are affected by incidents.

Can anyone advise where I am going wrong please?

Many thanks in advance.

Regards,

Lloyd

[Tom_N8]

Hi Lloyd,

Try with associating your Incident Number detail object with the merged dimension on Service.Then apply a filter on your table where Incident Number Detail Object Is Not Null:

This should return the desired result.

Kind regards,

Tom