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!!!!
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
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.
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'.
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.
Chris,
Yeah you're right - it is working now. Thanks for the help. :D