Stitching two queries in a report

[NiceCube]

Hi,

This is probably a daft question, so here goes:

I have two queries that join on a key value, however they both return differing numbers of rows as they're returning data from two very different areas of the business.  The user wants to see the data in the same report with the key values from each shown on the same row.  For example Q1 may return 4 rows whereas Q2 may returns 2 rows for the same key.  Naturally, the report will show more than one key value and the related data.

If this can be done can anyone explain the concepts behind it?

Thanks in advance,

Richard

[BigChris]

Hi Richard,

Can I just clarify what you're expecting to see in the output? Are you expecting to see 6 rows of data (i.e. a union between the two queries) or 4 rows (i.e. a join)? If it's the latter, which of your queries will be the master? In your example it would be Query 1, but will that always be the case?

[NiceCube]

Hi Chris,

Thanks for responding.

Hoping to see 4 rows (as in a left join).  Yes, the left query will always be the master.

Cheers,

Richard

[BigChris]

Hi Richard,

Ok, what I think you need to do is to bring a join in, which will create a Query 3. In the join info you'll need to specify the fields in the join (your key value) and the cardinality. In your case you're going to want 1:1 for Query1 and 0:1 for Query2. then pull in the relevant fields into Query3, make sure your list is using Query3 and Robert is your dad's brother.

[NiceCube]

Hi Chris,

Thanks for that.  I have two Uncle Rogers, and neither wanted to change his name, sadly.

Yes, tried a join as you describe but was hampered by lots of repeating data.  By that I mean if where query 1 has four rows (column 2 has four unique values) and query 2 has two rows (column 2 with two unique values) - I end up with eight rows of data.  It's kind of what I expected really and I may be trying to fit a square peg into a round hole.

Cheers,

Richard

[BigChris]

Hi Richard,

The doesn't really make sense...what you're describing sounds like a cross-join (I think it's also called a cartesian join), but if you're joining on your key value you should only get the four values from your first query.

[NiceCube]

Hi Chris,

Yes, a cross-join probably describes it.  I wanted the four values from from the first query, plus the two from the second - but all in four lines.  It could be the case that query two returns more rows than query one for a given key, and I'd want to show all values from both sides, with no repetition.  I don't think this can be done, even with having two lists in a two column table.

Thanks anyway,

Rich

[Mimo91]

Hi Richard,

Can you please share some mock up data set?

Set 1: Data set of Query 1 and Query 2 where Q1 has less row than Q2. Then manually make the data set which you finally want.
Set 2: Data set of Query 1 and Query 2 where Q2 has less row than Q1. Then manually make the data set which you finally want.

Just make the data sets with 5-10 rows. It would be helpful to understand the exact requirement.

Thanks.