Qliksense Equivalent to Sumproduct in excel

[Keitaru]

Using 2 columns criteria in my excel spreadsheet to find distinct value of my data set.

the sumproduct equation for DISTINCT Column is as such:
=IF(SUMPRODUCT(($B:$B=$B2)*($D:$D =$D2))>1,0,1)

[ B = Change ID ] & [ D = Task Type ]

Is there an equivalent function in Qliksense that I'm able to use to do something similar?

Table is as such

Class	Change ID	Task ID	Task Type	Distinct
Normal	CRQ000000010789	TAS000000009192	Normal	1
Standard	CRQ000000011647	TAS000000010132	Standard	0
Standard	CRQ000000011647	TAS000000010130	Standard	0
Standard	CRQ000000012004	TAS000000010565	Standard	1
Standard	CRQ000000012296	TAS000000011306	Standard	0
Standard	CRQ000000012296	TAS000000010910	Standard	0
Normal	CRQ000000013318	TAS000000012059	Normal	1
Normal	CRQ000000013393	TAS000000012359	Standard	0
Normal	CRQ000000013393	TAS000000012362	Standard	0
Normal	CRQ000000013393	TAS000000012365	Normal	1
Normal	CRQ000000013393	TAS000000012364	Standard	0
Normal	CRQ000000013725	TAS000000012596	Standard	1
Normal	CRQ000000013725	TAS000000012602	Normal	1
Standard	CRQ000000014029	TAS000000012811	Standard	1
Standard	CRQ000000014806	TAS000000013677	Standard	1
Normal	CRQ000000014845	TAS000000013714	Normal	1
Normal	CRQ000000015134	TAS000000014030	Standard	0
Normal	CRQ000000015134	TAS000000014022	Normal	1
Normal	CRQ000000015134	TAS000000014025	Standard	0
Standard	CRQ000000015664	TAS000000014591	Standard	1
Standard	CRQ000000016039	TAS000000015049	Standard	1
Standard	CRQ000000016091	TAS000000015102	Standard	1
Standard	CRQ000000016112	TAS000000015114	Standard	1
Standard	CRQ000000016207	TAS000000015227	Standard	1
Normal	CRQ000000014460	TAS000000014080	Standard	1
Normal	CRQ000000014460	TAS000000013945	Normal	1
Normal	CRQ000000014731	TAS000000013585	Normal	0
Normal	CRQ000000014731	TAS000000013588	Normal	0
Standard	CRQ000000014745	TAS000000013610	Standard	1
Standard	CRQ000000015599	TAS000000014523	Standard	1
Normal	CRQ000000010276	TAS000000011636	Normal	0
Normal	CRQ000000010276	TAS000000008821	Normal	0
Normal	CRQ000000010276	TAS000000011636	Normal	0
Normal	CRQ000000010276	TAS000000008821	Normal	0

Thank you.

[anushree1]

Could you explain what are F and W columns here, its not very clear as to what the sum product is trying to do to get the desired result

[Keitaru]

Basically I have a change management list of ID for Change Implementations where 1 change has multiple task ID attached to them I want to be able to see based on the Change ID and Task type, how can i see if the change task is a distinct one or not based on Change ID and Task type available.

E.g. 

I have multiple Changes taking place of which bulk of them are normal changes tagged to a single change ID while rest are Standard changes. 

Example of distinct Changes-Task

CRQ1 - Normal 

CRQ1 - Standard 

[JordyWegman]

Hi Keitaru,

As far as I know there isn't such a function, but you can solve it this way by counting the unique combinations:

Table:
Load * Inline [
Class	,	Change ID	,	Task ID	,	Task Type	,	Distinct
Normal	,	CRQ000000010789	,	TAS000000009192	,	Normal	,	1
Standard	,	CRQ000000011647	,	TAS000000010132	,	Standard	,	0
Standard	,	CRQ000000011647	,	TAS000000010130	,	Standard	,	0
Standard	,	CRQ000000012004	,	TAS000000010565	,	Standard	,	1
Standard	,	CRQ000000012296	,	TAS000000011306	,	Standard	,	0
Standard	,	CRQ000000012296	,	TAS000000010910	,	Standard	,	0
Normal	,	CRQ000000013318	,	TAS000000012059	,	Normal	,	1
Normal	,	CRQ000000013393	,	TAS000000012359	,	Standard	,	0
Normal	,	CRQ000000013393	,	TAS000000012362	,	Standard	,	0
Normal	,	CRQ000000013393	,	TAS000000012365	,	Normal	,	1
Normal	,	CRQ000000013393	,	TAS000000012364	,	Standard	,	0
Normal	,	CRQ000000013725	,	TAS000000012596	,	Standard	,	1
Normal	,	CRQ000000013725	,	TAS000000012602	,	Normal	,	1
Standard	,	CRQ000000014029	,	TAS000000012811	,	Standard	,	1
Standard	,	CRQ000000014806	,	TAS000000013677	,	Standard	,	1
Normal	,	CRQ000000014845	,	TAS000000013714	,	Normal	,	1
Normal	,	CRQ000000015134	,	TAS000000014030	,	Standard	,	0
Normal	,	CRQ000000015134	,	TAS000000014022	,	Normal	,	1
Normal	,	CRQ000000015134	,	TAS000000014025	,	Standard	,	0
Standard	,	CRQ000000015664	,	TAS000000014591	,	Standard	,	1
Standard	,	CRQ000000016039	,	TAS000000015049	,	Standard	,	1
Standard	,	CRQ000000016091	,	TAS000000015102	,	Standard	,	1
Standard	,	CRQ000000016112	,	TAS000000015114	,	Standard	,	1
Standard	,	CRQ000000016207	,	TAS000000015227	,	Standard	,	1
Normal	,	CRQ000000014460	,	TAS000000014080	,	Standard	,	1
Normal	,	CRQ000000014460	,	TAS000000013945	,	Normal	,	1
Normal	,	CRQ000000014731	,	TAS000000013585	,	Normal	,	0
Normal	,	CRQ000000014731	,	TAS000000013588	,	Normal	,	0
Standard	,	CRQ000000014745	,	TAS000000013610	,	Standard	,	1
Standard	,	CRQ000000015599	,	TAS000000014523	,	Standard	,	1
Normal	,	CRQ000000010276	,	TAS000000011636	,	Normal	,	0
Normal	,	CRQ000000010276	,	TAS000000008821	,	Normal	,	0
Normal	,	CRQ000000010276	,	TAS000000011636	,	Normal	,	0
Normal	,	CRQ000000010276	,	TAS000000008821	,	Normal	,	0
];


mapKey2_cntDistinct:
Mapping Load
	[Change ID] & '|' & [Task Type] as Key,
    1 as _cntDistinct
Where _cnt = 1
;    
Load
	[Change ID],
    [Task Type],
	Count([Change ID] & [Task Type]) as _cnt
Resident Table
Group by [Change ID], [Task Type]
;

NoConcatenate
Table3:
Load
	*,
    ApplyMap('mapKey2_cntDistinct',[Change ID] & '|' & [Task Type],0) as _cntDistinct
Resident Table
Order by [Change ID] asc, [Task ID] asc
;

Drop table Table;

Jordy

Climber

[anushree1]

Try Using:

if(aggr(count([Change ID]&[Task Type]),[Change ID]&[Task Type])=1,1,0)