Calculation from two different Queries with non equal colums

[Newbiee]

Hi all,

I am trying to do a calculation from two different queries but I am not able to get a result.

Here is my problem:

In querie 1 I make a total on a a column amount from table products.
In querie 2 I count the top 10 messages from table messages.
Both tables have no equal cells so i am not able to do a join.

Now I want to do a calculation like (quantity message 1/TOTAL amount) * 1000.
My aim is to normalise the messages per amount - so I can say on Tuesday I had a amount of 100.000 products and had 100 messages. Normalised on 1000 Products i had 1 message per 1000 products.

The problem is that I am not able to calculate this because i allways get a cross join error messages.

Can someone please let me know hot to do this right?

[wyconian]

Hi

You need to find some way of joining the queries so you need a common field.  Sounds like you may have date in both queries.  If you do then you can join on that.

If you don't have a common field you won't be able to join the queries and will get as cross product

[Newbiee]

Thanks for your answer!

Unfortuanetly even if there are common date fields I cant use them ...

Has someone else an other idea?

Is it maybe possible to store the result of the total in a variable and to use it with the other query?

[blom0344]

If both queries yield 1 returned row then you can add a dataitem that stores a constant (like 'X')
Use this one to perform a 1:1 join.
The same should be possible if one the queries returns 1 row and the other n rows, but your example does not make it clear what is exactly returned..

[Newbiee]

Your right - I think its better if I explain more detailled what my queries return.

Query one:

I take 5 colums from Table "Products" - every Product (line) has a "Timestart", "Timeend", "Amount Bags", "Amount Pieces" and a "Production line".

With my query I select for every "Production line" in a time interval (lets say one week) the "earliest timestart", the "latest timeend" and the total of "Amount Pieces".
The total of Amount pieces is interesting for me. (So i only have one row returned)

Query two:

I take three colums from Table "Messages" - every Message (line) has a "timestart", "production line" and a "Messagetext".

With my query I select for every "Production line" in a time interval (lets say one week) the amount of the same "messagetext". Depending on a prompt I take only the top 3,5 or 10 messages
The amount of messages is interesting for me. ( So i have 3 to 10 rows returned)

Sometimes I have a lot of products in one week, sometimes not. To be able to compare the numbers I get i want to have some characteristics.
Thats why I try to calculate:

Amount of Messages / Amount of pieces * 1.000

So i directly see if there have been many messages or only a few messages per product.

I hope this makes it easier for you to understand my problem!

[wyconian]

hi

from what you've said it looks you've got 2 common columns product line and timestart.  You could join the queries on these

[Newbiee]

Hi - okay there are equal colums but I cant use them.

Timestart has the same name but the entries are different (sometimes they have the same time but table "Products" has about 100 entries per day and table "Messages" has about 5000 entries a day).

And Product-Line has only two different entries Line1 and Line2.

[blom0344]

From your description I fathom that you can use the common dimension 'Product line' and 'Week'.
If you aggregate your measures to these dimensions you should be able to join (possibly a full outer join) over these dataitems.
In case of a full outer join you will need to use a coalesce function on the shared dimensions in the definition of the ultimate query.

[Newbiee]

hi - for everyone who has the same problem:

I found a practible solution by doing a drill-through [I did the drill-through in a button called "Next Page"]on the same report (but on page two) with the values calculated in query one.
Of course it is not the best way but it works fast!