If Statement in Script Counting dup values

[Anonymous]

I have the following IF Statement in my script

First 100.

IF(Reason = 'Go',1,0) as [GoReason_Count]

In my data I have a duplicate line and when I sum I'm getting 101 as my total. Is there a way to add something in my IF statement to change to only add a 1 in a distinct ID value?

ID 1 has a duplicate line. I realize I could load only distinct values into my query but trying to align with my chart expression

count({<Reason={[Go]}>}distinct ID)

	GoReason_Count	ID
Total	4
	2	1
	1	2
	1	3

Would like to see...

	GoReason_Count	ID
Total	3
	1	1
	1	2
	1	3

[swuehl]

Could you explain your data model a bit more detailed?

You can try to create the GoReason_Count flag in the dimension table with distinct ID values, if that is feasible, but without knowing your data model, it's hard to give any specific advice.

[Anonymous]

One table with ID being the primary key. This table has dup primary keys and is throwing my sum values off.

[swuehl]

Well, then ID is not a primary key, right?

You may achieve a script that creates a GoReason_Count flag only once for a single ID, but since the flag value is not unambiguos per ID then, you might run into trouble when you are filtering records (removing the flag though you would need to consider it in your sum).

[Anonymous]

True.
How would one go about creating a flag for a single ID in the script?

[Mark_Little · Accepted solution]

HI,

Make sure when you load you table by ID,

Then something like

  IF(RowNo =1,

          IF(Reason='GO',1,0),

          IF(ID = PREVIOUS(ID),

               0,

               IF(Reason='GO',1,0)

             )

     )  as [GoReason_Count]

Mark