topic If Statement in Script Counting dup values in QlikView

If Statement in Script Counting dup values

Anonymous — Wed, 18 Nov 2015 19:00:09 GMT

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

Re: If Statement in Script Counting dup values

swuehl — Wed, 18 Nov 2015 19:15:20 GMT

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.

Re: If Statement in Script Counting dup values

Anonymous — Wed, 18 Nov 2015 19:33:35 GMT

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

Re: If Statement in Script Counting dup values

swuehl — Wed, 18 Nov 2015 19:39:38 GMT

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).

Re: If Statement in Script Counting dup values

Anonymous — Thu, 19 Nov 2015 13:30:40 GMT

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

Re: If Statement in Script Counting dup values

Mark_Little — Thu, 19 Nov 2015 13:37:47 GMT

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),

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

)

) as [GoReason_Count]

Mark