Alteryx Community Designer Potential Solution
Created by: Michelle Mitchell-Lutz
Sr Business Analysis
C:\Users\Michelle.A.Mitchell\OneDrive - Quest Diagnostics\Downloads\Data.xlsx|||`Data$`
False
1
Data.xlsx
Table=`Data$`
Ref No - Ascending
Effective From - Descending
[Condition Type] != "Rent Payables"
AND
[Condition Type] != "Rent Refund"
Custom
!=
Condition Type
True
fixed
2022-11-28 08:49:14
0
2022-11-28 08:49:14
2022-11-28 08:49:14
[Condition Type] != "Rent Payables"
AND
[Condition Type] != "Rent Refund"
lookup
lookup
Condition Type
FindWhole
Append
IsEmpty([Filter])
Simple
IsEmpty
Filter
True
fixed
2022-11-28 09:34:10
0
2022-11-28 09:34:10
2022-11-28 09:34:10
IsEmpty([Filter])
Ignore
All
ByName
#2
#1
Filter
Ignore
All
ByName
Filter = "Select"
lookup = [Ref No]+"|"+[Condition Type]
lookup = Replace([looku...
Logic Test 1
,
Effective From
,
Valid To
Condition Type = if (!isnull([Effective From IFRS16_Rent_Payables]) AND !isnull(...
Formula
"Effective From " + [_CurrentField_]
Formula
"Valid To " + [_CurrentField_]
If there is an IFRS16 Rent Payables and an IFRS16 Rent Refund Condition Type for the same Ref No AND either the Effective From or Valid to dates are the same, then they cancel each other out and should therefore be discarded, leaving the Rent Payables Condition Type (in the below example this is true for 137972)
!IsEmpty([Condition Type])
Simple
IsNotEmpty
Condition Type
True
fixed
2022-11-28 09:10:18
0
2022-11-28 09:10:18
2022-11-28 09:10:18
!IsEmpty([Condition Type])
Logic Test 2
,
Effective From
,
Valid To
Condition Type = if (!isnull([Effective From Rent_Payables]) AND !isnull([Effect...
Formula
"Effective From " + [_CurrentField_]
Formula
"Valid To " + [_CurrentField_]
If there is a Rent Payables and a Rent Refund Condition Type for the same Ref No AND either the Effective From or Valid to dates are the same, then they cancel each other out and should therefore be discarded, leaving the IFRS16 Rent Payables Condition Type (in the below example this is true for 200672)
!IsEmpty([Condition Type])
Simple
IsNotEmpty
Condition Type
True
fixed
2022-11-28 09:10:22
0
2022-11-28 09:10:22
2022-11-28 09:10:22
!IsEmpty([Condition Type])
Horizontal
Discarding rows based on combination of values - Solution MML