Concatenated Columns (Dupe and Non-Dupe Rows)

[Web2CRM]

Hello Cognoise Gurus,

Good Day...

I need your insights/help in my requirement (report studio - list report), details below:

1.) I have 4 columns in the report page with prompt selection value of 'Dupe' and 'Non-Dupe' with 'Non-Dupe' as default.

     Dtl Key            Oppty#          Svc Line           Value($)
     DTR003           AZ-X03           SVC003            300
     DTR001           AZ-X01           SVC001            100
     DTR001           AZ-X01           SVC001            100
     DTR002           AZ-X02           SVC002            200


2.) I need to concatenate the first 3 columns (Dtl key, Oppty# & Svc Line), considering they have diff. data types.
     Dtl Key            Oppty#          Svc Line           Value($)        Concatenated Field
     DTR003           AZ-X03           SVC003            300               DTR003AZ-X03SVC003
     DTR001           AZ-X01           SVC001            100               DTR001AZ-X01SVC001
     DTR001           AZ-X01           SVC001            100               DTR001AZ-X01SVC001
     DTR002           AZ-X02           SVC002            200               DTR002AZ-X02SVC002

3.) When the report runs, it should be:
     1.) Sorted in Ascending order
     2.) If concatenated value first row is equal to second row then the second row will be tag as 'Dupe' else 'Non-Dupe'
     3.) For 'Dupe' rows, the value($) will not be counted.

     Dtl Key            Oppty#          Svc Line           Value($)        Concatenated Field                 Results               New Value
     DTR001           AZ-X01           SVC001            100               DTR001AZ-X01SVC001            Non-Dupe          100
     DTR001           AZ-X01           SVC001            100               DTR001AZ-X01SVC001            Dupe                      0
     DTR002           AZ-X02           SVC002            200               DTR002AZ-X02SVC002            Non-Dupe           200
     DTR003           AZ-X03           SVC003            300               DTR003AZ-X03SVC003            Non-Dupe           300

Thanks in advanced!!!!

[BigChris]

Now that's how a question should look!

First off, the concatenation of your fields should be a problem. They're both text fields, so they should join together quite happily.

The second half of your question is a bit more tricky, but should still be ok. I haven't tried it out yet (got to get ready for a meeting), but you should be able to do something along the lines of:
if(running-count(concatenated field)=1) then ('Non-Dupe') else ('Dupe')
if(running-count(concatenated field)=1) then (Value) else (0)

Have a go with that and post back if it doesn't do what you're expecting

C

[Web2CRM]

Hi Chris,

Thanks for promptly providing your help, I tried your suggestion below however, only the first row shows 'Non-Dupe' the rest of the rows shows 'Dupe' even though the data is unique/distinct.

I concatenate the columns by simple combining them in new data item with expression [Data Item1] + [Data Item2] + [Data Item3].

I am not sure if it has something to do/impact with the running-count result.

[Web2CRM]

I tried using the running-count([concatenated field]) to see what is the value it return and it seems like it just count the number of rows for [concatenated field]; reason why only the first row show as 'Non-Dupe' and the rest are showing 'Dupe'.

[BigChris]

Ahhh...I think I answered too quickly. When you think about it, it makes sense that the first row would be the only one that shows 'Non-Dupe' given the calculation I gave you. What you actually need is something more along the lines of

if(running-count([Dtl Key] for [concatenated field])=1) then ('Non-Dupe') else ('Dupe')

Essentially you need the count to start again when it gets to the next concatenated-field...my fault. Give that a whirl and see if that fixes the problem.

[Web2CRM]

Chris,

Yeah you're right - it is working now.  Thanks for the help.  :D