topic Flagging duplicates? in QlikView

Flagging duplicates?

Tue, 23 Jun 2015 18:06:40 GMT

Hey,

Need help with a concatenation of three dimensions, Name, Type, and Date and need to flag a duplicate spend record that corresponds to the concatenation.

Ex:

Owner - John Doe

Type - Lodging

Date - 6/23/2015

Two records found for $175 dollars with the information above.

Re: Flagging duplicates?

Clever_Anjos — Tue, 23 Jun 2015 18:37:46 GMT

tmp:

LOAD

Owner ,

Type ,

Date ,

count(Owner) as qty

resident yourtable;

LEFT join (yourtable)

LOAD

Owner ,

Type ,

Date

1 as FlgDup

Resident tmp

where qty > 1;

drop table tmp;

Re: Flagging duplicates?

MK_QSL — Tue, 23 Jun 2015 18:37:56 GMT

Temp:

Load Owner, Type, Date, Owner&Type&Date as Key, Amount From ....

Final:
Load

If(Key = Previous(Key) and Amount = Previous(Amount),1,0) as DuplicateFlag

Resident Temp Order By Key, Amount;

Drop Table Temp;

Re: Flagging duplicates?

Anonymous — Tue, 23 Jun 2015 18:51:51 GMT

I guess the Amount should be also included in the key in this case.

Re: Flagging duplicates?

Tue, 23 Jun 2015 19:10:12 GMT

Ideally, the amount needs to be in the key as well to flag the exact same transaction, but then that brings up another issue as to how to visualize it in a dashboard. This part is saying invalid for me:

tmp:

LOAD

Owner ,

Type ,

Date ,

count(Owner) as qty

resident yourtable;

Error is invalid expression

Re: Flagging duplicates?

Anonymous — Tue, 23 Jun 2015 19:29:54 GMT

See if the attached helps...