Same Transcation, Same Person, Different Company - Qlik Community

Anonymous · ‎2017-03-01

Hello Experts all around the world!

I need help to flag out Transaction with same bidder but different Company.

With above example data, 2 transaction (1001 ad 1005) will be flag out.

Transaction 1001: Same bidder, Different Company :1

Transacion 1004: Same bidder, Same company: 0

Transaction 1005: Same bidder, Different Company: 1

So my KPI count will be 2.

I would prefer to have my expression to load in KPI expression instead of data load.

Regards,

Jia

sunny_talwar · ‎2017-03-01

May be like this

Sum(Aggr(If(Count(DISTINCT Company) > 1, 1), Transaction, Bidder))

Anonymous · ‎2017-03-02

Hi Sunny,

Could you explain what it does?

sunny_talwar · ‎2017-03-03

With Transaction as your dimension, this will only show you 1 for those Transactions which have more than 1 company involved in those transactions.

Does it make sense? Is it not working?

nav_pienaar · ‎2017-03-03

Hi Sunny

Not sure if this is what you are looking for.

Try this in the script and then do a Sum(Flag_Dup) in the font end.

Hope it work

Nav

TEMP:

Load

Row,

Bidder & '_' & Company as Key_Bidder_Comp,

Transaction & '_' & Bidder as Sort_Key_Trans_Bidder,

Transaction,

Bidder,

Company

INLINE [

Row, Transaction, Bidder, Company

1,1001, AA, XYZ

2,1001, AA, KID

3,1004, DD, YOO

4,1004, DD, YOO

5,1005, EE, JOK

6,1005, EE, JOK

7,1005, EE, QID

];

Temp_02:

Load

* ,

if(RowNo() =1,0,

if(Key_Bidder_Comp = Previous(Key_Bidder_Comp),1,0)) as Flag_Dup

Resident TEMP

Order by Sort_Key_Trans_Bidder;

Drop Table TEMP;