Hey Guys,
I'm pretty new to Cognos and I was wondering if anyone knows how I might accomplish a certain task:
In query A, we have customer information.
In query B, we have pricing records.
I need to create a variable that returns a 'Y' for each Customer record returned in query A as long as that customer is found in query B.
Does anyone know how that might be handled using a boolean variable? If I cannot accomplish this through a boolean variable, then how else can I create this?
Thanks a ton!
Quote from: thoros on 09 Jun 2021 10:52:37 AM
Hey Guys,
I'm pretty new to Cognos and I was wondering if anyone knows how I might accomplish a certain task:
In query A, we have customer information.
In query B, we have pricing records.
I need to create a variable that returns a 'Y' for each Customer record returned in query A as long as that customer is found in query B.
Does anyone know how that might be handled using a boolean variable? If I cannot accomplish this through a boolean variable, then how else can I create this?
Thanks a ton!
Hi,
One important question that occurs is what do you need this variable for? Is it to display the Y values in the report? To conditionally show or hide something? To conditionally highlight something? Something else?
I'm just trying to get a feel for whether you need a variable or something different.
In terms of what you're asking, you could add a join object in the Query Explorer that joins your two queries using an inner join, and use a third query to show the joined results. Any rows in this third query would then by definition have records in both of your original queries. You could then create a calculation or a variable (or whatever you need) based off this third query?
Cheers!
MF.
Quote
Hi,
One important question that occurs is what do you need this variable for? Is it to display the Y values in the report? To conditionally show or hide something? To conditionally highlight something? Something else?
I'm just trying to get a feel for whether you need a variable or something different.
In terms of what you're asking, you could add a join object in the Query Explorer that joins your two queries using an inner join, and use a third query to show the joined results. Any rows in this third query would then by definition have records in both of your original queries. You could then create a calculation or a variable (or whatever you need) based off this third query?
Cheers!
MF.
Hey MF,
Appreciate you taking the time to respond! To answer your question, I need this variable to simply return a 'Y' for each customer generated in query A granted they have an appearance in query B! Some of the customers in query A will not having pricing records in query B; in that case, we can return an 'N' or just a blank.
The final query, query C, will look like this below:
Customer# Pricing Records?
12345 Y
67891 Y
54321 N
I think the join would work however my hesitation is the syntax for the boolean variable. I'm not really sure how to write an expression that just says:
IF queryA.Company# and queryA.Cust# found in queryB then set boolean variable to 'Y' else set boolean variable to 'N'
Let me know if I can provide further details,
TJ
Quote from: thoros on 10 Jun 2021 08:11:15 AM
Hey MF,
Appreciate you taking the time to respond! To answer your question, I need this variable to simply return a 'Y' for each customer generated in query A granted they have an appearance in query B! Some of the customers in query A will not having pricing records in query B; in that case, we can return an 'N' or just a blank.
The final query, query C, will look like this below:
Customer# Pricing Records?
12345 Y
67891 Y
54321 N
I think the join would work however my hesitation is the syntax for the boolean variable. I'm not really sure how to write an expression that just says:
IF queryA.Company# and queryA.Cust# found in queryB then set boolean variable to 'Y' else set boolean variable to 'N'
Let me know if I can provide further details,
TJ
Hi,
If it's just to display an item with a Y or N, then a variable (or at least what Cognos terms a variable) isn't needed here. The approach I would take is:
In the Query Explorer, add a Join object from the toolbar. Drag your Query A and Query B into the inputs of the join, and add a new query (C) as the output of the join. Pick your item or items to join the queries on, and set the cardinality of the join to be 1..1 <---> 0..1 so that you get all rows from Query A regardless of whether there are corresponding rows in Query B (a left outer join). Make sure Query C contains all the items from Query A and at least one item from Query B.
Base your reporting object (a List I'm guessing) on Query C, and add a query calculation that has the expression
if ([the item that came in to Query C from Query B] is null) then ('N') else ('Y)
Then use this query calculation in your list.
Hopefully that should give you what you need?
Cheers!
MF.