Topic
  • 8 replies
  • Latest Post - ‏2012-11-21T22:09:09Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts

Pinned topic How to remove the repeated rows when classfying products?

‏2012-11-15T21:51:22Z |
Hi,

I am using Cognos Report Studio 8.4 to extract the sales data for validation purpose.

The data should be pulled out like:
Product Class Type
Prom
Non-Prom

Here, Product Class Type means that: if Product Category Number = 999, then it refers to 'Prom'; Otherwise, it is classified as 'Non-Prom'.

For achieving this goal, I used 2 steps for Product Class Type:
1). Used the following expression
{Code}
CASE
WHEN Product Category Number = '999' THEN 'Prom'
ELSE 'Non-Prom'
END
{Code}

2). Used the aggregation property as Automatic

But, the results I got for the 2 product classes are repeated 6 times.
For example, the result I expect to see is like:
Product Class Type
Prom
Non-Prom

But, I got:
Product Class Type
Prom
Non-Prom
Prom
Non-Prom
Prom
Non-Prom
Prom
Non-Prom
Prom
Non-Prom
Prom
Non-Prom
How to deal with this problem and remove the repeated rows?

Thank you in advance.
Updated on 2012-11-21T22:09:09Z at 2012-11-21T22:09:09Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to remove the repeated rows when classfying products?

    ‏2012-11-16T01:38:36Z  
    Hi,
    I think because of the product number in the query its repeating.

    You can do it in simple way like
    1)Drag new query to query explorer.
    2)Reference old query to this new query
    3)Drag data items except product number into new query.
    4)Point this new query to your report and run.

    thanks,
    Suresh.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to remove the repeated rows when classfying products?

    ‏2012-11-16T21:55:47Z  
    Hi,
    I think because of the product number in the query its repeating.

    You can do it in simple way like
    1)Drag new query to query explorer.
    2)Reference old query to this new query
    3)Drag data items except product number into new query.
    4)Point this new query to your report and run.

    thanks,
    Suresh.
    Thank you Suresh_BI.

    Will try this next Monday.

    Good weekend.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to remove the repeated rows when classfying products?

    ‏2012-11-16T21:58:37Z  
    Hi,
    I think because of the product number in the query its repeating.

    You can do it in simple way like
    1)Drag new query to query explorer.
    2)Reference old query to this new query
    3)Drag data items except product number into new query.
    4)Point this new query to your report and run.

    thanks,
    Suresh.
    One more thing, how to *2) Reference old query to this new query*?

    Thank you.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to remove the repeated rows when classfying products?

    ‏2012-11-17T12:34:22Z  
    One more thing, how to *2) Reference old query to this new query*?

    Thank you.
    Hi,
    Attached report specification for how to reference the base query to another query?

    thanks,
    Suresh.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to remove the repeated rows when classfying products?

    ‏2012-11-19T15:10:45Z  
    Hi,
    Attached report specification for how to reference the base query to another query?

    thanks,
    Suresh.
    Thank you Suresh_BI.

    Forgive my bluntness.

    How to view the content of the attachment "Query Reference.txt"?

    I only find some codes like in XLM format ...
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to remove the repeated rows when classfying products?

    ‏2012-11-19T15:31:53Z  
    Thank you Suresh_BI.

    Forgive my bluntness.

    How to view the content of the attachment "Query Reference.txt"?

    I only find some codes like in XLM format ...
    Hi,
    Please take a look into attached image file.

    thanks,
    Suresh.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to remove the repeated rows when classfying products?

    ‏2012-11-20T21:58:56Z  
    Hi,
    Please take a look into attached image file.

    thanks,
    Suresh.
    Thank you Suresh_BI agian.

    But how to make Query 1 arrow at Query 2?
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: How to remove the repeated rows when classfying products?

    ‏2012-11-21T22:09:09Z  
    Thank you Suresh_BI agian.

    But how to make Query 1 arrow at Query 2?
    Understand now.

    Testing ...