Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Qliksense Equivalent to Sumproduct in excel

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

ClassChange IDTask IDTask TypeDistinct
NormalCRQ000000010789TAS000000009192Normal1
StandardCRQ000000011647TAS000000010132Standard0
StandardCRQ000000011647TAS000000010130Standard0
StandardCRQ000000012004TAS000000010565Standard1
StandardCRQ000000012296TAS000000011306Standard0
StandardCRQ000000012296TAS000000010910Standard0
NormalCRQ000000013318TAS000000012059Normal1
NormalCRQ000000013393TAS000000012359Standard0
NormalCRQ000000013393TAS000000012362Standard0
NormalCRQ000000013393TAS000000012365Normal1
NormalCRQ000000013393TAS000000012364Standard0
NormalCRQ000000013725TAS000000012596Standard1
NormalCRQ000000013725TAS000000012602Normal1
StandardCRQ000000014029TAS000000012811Standard1
StandardCRQ000000014806TAS000000013677Standard1
NormalCRQ000000014845TAS000000013714Normal1
NormalCRQ000000015134TAS000000014030Standard0
NormalCRQ000000015134TAS000000014022Normal1
NormalCRQ000000015134TAS000000014025Standard0
StandardCRQ000000015664TAS000000014591Standard1
StandardCRQ000000016039TAS000000015049Standard1
StandardCRQ000000016091TAS000000015102Standard1
StandardCRQ000000016112TAS000000015114Standard1
StandardCRQ000000016207TAS000000015227Standard1
NormalCRQ000000014460TAS000000014080Standard1
NormalCRQ000000014460TAS000000013945Normal1
NormalCRQ000000014731TAS000000013585Normal0
NormalCRQ000000014731TAS000000013588Normal0
StandardCRQ000000014745TAS000000013610Standard1
StandardCRQ000000015599TAS000000014523Standard1
NormalCRQ000000010276TAS000000011636Normal0
NormalCRQ000000010276TAS000000008821Normal0
NormalCRQ000000010276TAS000000011636Normal0
NormalCRQ000000010276TAS000000008821Normal0

 

Thank you.

 

 

4 Replies
Highlighted
Specialist II
Specialist II

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

Highlighted
Creator
Creator

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 

Highlighted
Partner
Partner

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

Work smarter, not harder
Highlighted
Specialist II
Specialist II

Try Using:

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