Hello,
in my project, we pay bills. I would like to have a table with for every project the amount of money paid befor 2019 and the money paid in 2019.
I made two queries, one correctly summing up the amount in the previous years, one summing up the amount this year.
To get these dates in a single table I want to join them.
Then it turns out, that whatever cardinality I choose, the sums in the join mix up, when there have been more then one payment, either befor or in the year 2019:



The first table shows, that there was 11.750 € paid in 5 payments, the second table, that there was 4.690 € paid in 2 payments.
The third table shows the result of the join. Now it shows, that 5x4.690 = 23.450 and 2x11.750=23.500.

Could someone here either show me a better solution to get the table I want or explain to me, what I should/could change in the join?

Thanks in advance

------------------------------
Ludwig Leijten
------------------------------

#CognosAnalyticswithWatson

I'm assuming that you considered and rejected using a union.  Could you explain your rationale for that as, given the information that I have, it would seem to be what you might want to use.

------------------------------
IAN HENDERSON
------------------------------

Hello Ian,
I didn't consider Union.
How I understand Union is, that I would then get one row for every payment.
What I would like to have is one row per project with in different columns the sum of the payments befor 2019 und the sum of the payments in 2019.
I'm rather new to cognos, but from former experiences I would try that with a join.

I'm happy with any other solution as well, when you are able to do this with a union, please let me know.

------------------------------
Ludwig Leijten
------------------------------

Original Message:
Sent: Tue May 05, 2020 07:41 PM
From: IAN HENDERSON
Subject: Join doesn't work as I would expect

I'm assuming that you considered and rejected using a union.  Could you explain your rationale for that as, given the information that I have, it would seem to be what you might want to use.

------------------------------
IAN HENDERSON
------------------------------

A union contains all rows from both tables. If there are duplicates, only one is included in the union.   If I understand correctly what you are trying to accomplish, that is what you would want to do.  

A join is a specification of the relationship between the objects in a table/query/rectangle of data to those in another table/query/rectangle of data.  This is somewhat analogous to the general relational data base concept.

------------------------------
IAN HENDERSON
------------------------------

Original Message:
Sent: Fri May 08, 2020 10:23 AM
From: Ludwig Leijten
Subject: Join doesn't work as I would expect

Hello Ian,
I didn't consider Union.
How I understand Union is, that I would then get one row for every payment.
What I would like to have is one row per project with in different columns the sum of the payments befor 2019 und the sum of the payments in 2019.
I'm rather new to cognos, but from former experiences I would try that with a join.

I'm happy with any other solution as well, when you are able to do this with a union, please let me know.

------------------------------
Ludwig Leijten
------------------------------


Original Message:
Sent: Tue May 05, 2020 07:41 PM
From: IAN HENDERSON
Subject: Join doesn't work as I would expect

I'm assuming that you considered and rejected using a union.  Could you explain your rationale for that as, given the information that I have, it would seem to be what you might want to use.

------------------------------
IAN HENDERSON

Hello Ian,
unfortunately, that is not what I want.
I would like to have the following:

	Sum of all the Payments befor 2019	Sum of all the Payments in 2019
Projekt A
Projekt B
…
Projekt X

I used two queries, one for the "befor 2019" column, one for the "in 2019" column and now I would like to have them in one table. As written, I would think this is a join, but it doesn't work at all.
Regards

------------------------------
Ludwig Leijten
------------------------------

Original Message:
Sent: Fri May 08, 2020 12:42 PM
From: IAN HENDERSON
Subject: Join doesn't work as I would expect

A union contains all rows from both tables. If there are duplicates, only one is included in the union.   If I understand correctly what you are trying to accomplish, that is what you would want to do.  

A join is a specification of the relationship between the objects in a table/query/rectangle of data to those in another table/query/rectangle of data.  This is somewhat analogous to the general relational data base concept.

------------------------------
IAN HENDERSON
------------------------------


Original Message:
Sent: Fri May 08, 2020 10:23 AM
From: Ludwig Leijten
Subject: Join doesn't work as I would expect

Hello Ian,
I didn't consider Union.
How I understand Union is, that I would then get one row for every payment.
What I would like to have is one row per project with in different columns the sum of the payments befor 2019 und the sum of the payments in 2019.
I'm rather new to cognos, but from former experiences I would try that with a join.

I'm happy with any other solution as well, when you are able to do this with a union, please let me know.

------------------------------
Ludwig Leijten


Original Message:
Sent: Tue May 05, 2020 07:41 PM
From: IAN HENDERSON
Subject: Join doesn't work as I would expect

I'm assuming that you considered and rejected using a union.  Could you explain your rationale for that as, given the information that I have, it would seem to be what you might want to use.

------------------------------
IAN HENDERSON

I couldn't figure out how to get the joined query to work in authoring. I didn't spend much time trying to do as this is just break-time activity. I was able to do so in a module.

I created a module and created a 1.1 relationship between ep2018 and ep2019 using Aktenzeichen as the key.

But it will only work if and only if there is a value for an Aktenzeichen in each table. It is possible, even probable, the case that in your data this situation of values in only one place happens too.

This is why you need to consider to use a union.

I created the files ep2018_1 and ep2019_1. Each has a record for an Aktenzeichen which does not exist in the other csv.

I added a Jahr column. It will allow you to track your time information.

I created a module and created a union with ep2018_1 and ep2019_1

I then created a crosstab using Aktenzeichen as the rows, AP_Auszahlungbetrage nested under Jahr in the columns.

I think that will achieve what you need.

I don't know the source of your data so it is possible that in source there exists similar information. Indeed it is possible that the source of your two tables has the information you need in the same table.

I did not try to create the union in authoring but I don't think it would prove too much more difficult than in modelling.  In general, I think this sort of thing is probably better done in a modelling tool rather than authoring but that could be attributed to the  old adage, 'where you stand depends on where you sit'.

------------------------------
IAN HENDERSON
------------------------------

Original Message:
Sent: Mon May 11, 2020 02:46 AM
From: Ludwig Leijten
Subject: Join doesn't work as I would expect

Hello Ian,
unfortunately, that is not what I want.
I would like to have the following:

	Sum of all the Payments befor 2019	Sum of all the Payments in 2019
Projekt A
Projekt B
…
Projekt X

I used two queries, one for the "befor 2019" column, one for the "in 2019" column and now I would like to have them in one table. As written, I would think this is a join, but it doesn't work at all.
Regards

------------------------------
Ludwig Leijten
------------------------------


Original Message:
Sent: Fri May 08, 2020 12:42 PM
From: IAN HENDERSON
Subject: Join doesn't work as I would expect

A union contains all rows from both tables. If there are duplicates, only one is included in the union.   If I understand correctly what you are trying to accomplish, that is what you would want to do.  

A join is a specification of the relationship between the objects in a table/query/rectangle of data to those in another table/query/rectangle of data.  This is somewhat analogous to the general relational data base concept.

------------------------------
IAN HENDERSON


Original Message:
Sent: Fri May 08, 2020 10:23 AM
From: Ludwig Leijten
Subject: Join doesn't work as I would expect

Hello Ian,
I didn't consider Union.
How I understand Union is, that I would then get one row for every payment.
What I would like to have is one row per project with in different columns the sum of the payments befor 2019 und the sum of the payments in 2019.
I'm rather new to cognos, but from former experiences I would try that with a join.

I'm happy with any other solution as well, when you are able to do this with a union, please let me know.

------------------------------
Ludwig Leijten


Original Message:
Sent: Tue May 05, 2020 07:41 PM
From: IAN HENDERSON
Subject: Join doesn't work as I would expect

I'm assuming that you considered and rejected using a union.  Could you explain your rationale for that as, given the information that I have, it would seem to be what you might want to use.

------------------------------
IAN HENDERSON

I would try a Stitch Query.  See Link below.

I have had similar issues with numbers. 

https://www.ibm.com/developerworks/data/library/cognos/reporting/advanced_report_design/page605.html

------------------------------
Kristie Walsh
------------------------------

You can probably achieve what you need in a single query.  Given that your output looks like a List, this is what I suggest.
Create one calculation for your measure for the year 2019: if(year(date) = 2019) then (measure) else (0)
Create one calculation for the measure for all other years: if(year(date) < 2019) then (measure) else (0)

I know this is a simplified approach, but works in most situations.  Good luck!

------------------------------
Albert Valdez
albert@intelalytics.com
------------------------------

Hello all,
thank you for your answers.
In the end, the answer von Albert Valdez worked. Although it is a workaround, it gives me the right answer. Thanks!

I recently learned, that my service-provider hasn't update my Cognos recently. I will first wait for him to do that, before I try any other join. Perhaps that might also be part of the problem.

Regards

------------------------------
Ludwig Leijten
------------------------------

Original Message:
Sent: Fri May 15, 2020 05:55 PM
From: Albert Valdez
Subject: Join doesn't work as I would expect

You can probably achieve what you need in a single query.  Given that your output looks like a List, this is what I suggest.
Create one calculation for your measure for the year 2019: if(year(date) = 2019) then (measure) else (0)
Create one calculation for the measure for all other years: if(year(date) < 2019) then (measure) else (0)

I know this is a simplified approach, but works in most situations.  Good luck!

------------------------------
Albert Valdez
albert@intelalytics.com
------------------------------

Glad it worked Ludwig, thanks for sharing the solution!
If you need other help with issues or training, please let me know!

------------------------------
Albert Valdez
albert@intelalytics.com
------------------------------

Original Message:
Sent: Mon May 18, 2020 06:07 AM
From: Ludwig Leijten
Subject: Join doesn't work as I would expect

Hello all,
thank you for your answers.
In the end, the answer von Albert Valdez worked. Although it is a workaround, it gives me the right answer. Thanks!

I recently learned, that my service-provider hasn't update my Cognos recently. I will first wait for him to do that, before I try any other join. Perhaps that might also be part of the problem.

Regards

------------------------------
Ludwig Leijten
------------------------------


Original Message:
Sent: Fri May 15, 2020 05:55 PM
From: Albert Valdez
Subject: Join doesn't work as I would expect

You can probably achieve what you need in a single query.  Given that your output looks like a List, this is what I suggest.
Create one calculation for your measure for the year 2019: if(year(date) = 2019) then (measure) else (0)
Create one calculation for the measure for all other years: if(year(date) < 2019) then (measure) else (0)

I know this is a simplified approach, but works in most situations.  Good luck!

------------------------------
Albert Valdez
albert@intelalytics.com