Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
Try Using:
if(aggr(count([Change ID]&[Task Type]),[Change ID]&[Task Type])=1,1,0)