New to report studio. Finding Duplicates but not deleting them

[jdmas]

This is my dilemma, I am trying to find duplicate purchasing ID's and have them labeled as Multi in the next column. This is what I have so far:

if(count (distinct [PURCHASE_ID] ) >1 ) THEN ('MULTI') ELSE ('').

This code does not return an error message, but every row in the column now has Multi in it. I am not sure what I am missing here, so if anyone could help me, I would greatly appreciate it!

[BigChris]

If you put another column in to calculate

count(distinct [PURCHASE_ID])

what do you get? Also, what's the data like that you're querying? If you're running against something like purchase order lines you're likely to get duplicates...you need to be running against your buyer table (or whatever it's called in your system). You're probably already doing that, but it's worth stating the obvious sometimes...

[jdmas]

I do have the formula in another column. What I am trying to do is write a formula that identifies the duplicates and leaves the other cells blank. So when I enter this formula in another column and export it to excel, every row in that column is saying "Multi".

[BigChris]

Yes, I understood that from your original post. What I'm trying to get at is that the nature of your data will be driving the output of your calculation. Without having sight of your data it's difficult to help with your calculation, which is why I was asking you share the results of

count(distinct [PURCHASE_ID])

[jdmas]

The result I am getting from using count(distinct [PURCHASE_ID]) is "Multi" in every row. Here is an example as to how my output looks:

Purchase ID
12345
12346
12346
12347
12348
12349
12349
12349

So my goal is to have a multi column find the duplicate Purchase ID's and have Multi written next to them. So like
Purchase ID          Multi
12345               
12346                  Multi
12346                  Multi
12347
12348
12349                  Multi
12349                  Multi
12349                  Multi

I hope this clears things up.

[MFGF]

The result I am getting from using count(distinct [PURCHASE_ID]) is "Multi" in every row. Here is an example as to how my output looks:

Purchase ID
12345
12346
12346
12347
12348
12349
12349
12349

So my goal is to have a multi column find the duplicate Purchase ID's and have Multi written next to them. So like
Purchase ID          Multi
12345               
12346                  Multi
12346                  Multi
12347
12348
12349                  Multi
12349                  Multi
12349                  Multi

I hope this clears things up.

Nope - I don't think it does. The result you posted is what comes back from the expression:

if(count (distinct [PURCHASE_ID] ) >1 ) THEN ('MULTI') ELSE ('').

What Chris is asking is what comes back from using the expression

count(distinct [PURCHASE_ID])

Can you advise?

Cheers!

MF.

[jdmas]

The results I get are this from the count(distinct [PURCHASE_ID]):

Purchase ID          Multi
12345                  1,479
12346                  1,479
12346                  1,479
12347                  1,479
12348                  1,479
12349                  1,479
12349                  1,479
12349                  1,479

[Robl]

I think the problem is that your grouping by purchase_id but you also want to count it.

I'm thinking that the best way to do this would be to create a second instance of purchase ID called Purchase_ID_Group

Then create a column for count(purchase_ID for purchase_id_Group) and nest that within Purchase_ID_Group but set the group span to be purchase_id.
Then add a calculation to do with the count being > 1.

Your data will then look a bit like this;
Purchase ID Multi
12345       12345      1   12345
12346      12346      2   Multi
      12346       2   Multi
12347      12347      1
12348      12348      1
12349       12349      3   Multi
      12349       3   Multi
      12349       3   Multi

I've not tried this on a model of my own, but I think it will work, or at least might nudge you in the right direction for a solution.

[jdmas]

It worked! Thank you so much!

[jdmas]

Thanks for all the help everyone. I am new to all of this and want to learn as much as I can so I don't have to keep bugging all of you for help! Does anyone happen to know of a good website that defines the functions for report studio (or sql for that matter)?

[MFGF]

Thanks for all the help everyone. I am new to all of this and want to learn as much as I can so I don't have to keep bugging all of you for help! Does anyone happen to know of a good website that defines the functions for report studio (or sql for that matter)?

Hi,

The RS functions (and summaries and constructs) are all documented in the Report Studio User Guide:

http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.2.1.1/ug_cr_rptstd.pdf

Look at Appendix F: Using the expression editor

Cheers!

MF.