Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
8 replies Latest Post - ‏2012-11-21T22:09:09Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

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

    ‏2012-11-16T01:38:36Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

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

      ‏2012-11-16T21:55:47Z  in response to SystemAdmin
      Thank you Suresh_BI.

      Will try this next Monday.

      Good weekend.
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

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

      ‏2012-11-16T21:58:37Z  in response to SystemAdmin
      One more thing, how to *2) Reference old query to this new query*?

      Thank you.
      • SystemAdmin
        SystemAdmin
        15496 Posts
        ACCEPTED ANSWER

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

        ‏2012-11-17T12:34:22Z  in response to SystemAdmin
        Hi,
        Attached report specification for how to reference the base query to another query?

        thanks,
        Suresh.
        • SystemAdmin
          SystemAdmin
          15496 Posts
          ACCEPTED ANSWER

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

          ‏2012-11-19T15:10:45Z  in response to SystemAdmin
          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 ...