Double Counting

Wed, 15 Sep 2010 19:05:04 GMT

I am having an issue and i am not sure the best way to deal with it. The solution should be easy but i am just having a brain freeze.

So when a Tap Number is created it has a ALLOW_AMOUNT and is stored in one table. Once the TAP_EVENT is approved that ALLOW_AMOUNT becomes and AUTHORIZED AMOUNT and the record is removed and stored in a separate table. Unless of course there is a bug in the system that every once in a while doesn't do what it is supposed to do (as in record 21003500) where it copies to the new table but doesn't delete out of the old one [:@]. So when I concatentate and try to get a total "Amount" of both ALLOW_AMOUNT and AUTHORIZED AMOUNT 21003500 is approx double what it should be. If there is an Autorized Amount i just want that and the ALLOW_AMOUNT should be deleted.

They are two separate table that are just being concatenated nothing else. I have tried WHERE NOT EXISTS(TAP_EVENT_NUMBER) but the problem is that sometimes a TAP_EVENT can be divided into approved and none approved PACK_GROUP_IDs.

ANy suggestions?

TAP_EVENT_NBR	PACK_GROUP_ID	ALLOW_AMOUNT	AUTHORIZED_AMOUNT
21000024	142		4254.24
21000024	121		6583.68
21000024	168		7689.6
21000024	172		20339.2
21000028	168		32249.4
21000028	AC		45867.6
21000028	142		50398.02
21000028	200		120018.48
21000028	169		120295.76
21000028	172		248411.52
21000028	107		312750.48
21000028	TQ		315563.88
21003500	TR	45810.52
21003500	LI	166989.5
21003500	TR		45810.52
21003500	LI		119700

topic Double Counting in QlikView

Double Counting