Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need to evaluate a set of data criteria to assign buckets to each transaction record. Each row could potentially meet criteria for multiple buckets. I cannot hardcode these rules with if-then-else as they will change frequently with rules being added and removed.
The result needs to be persisted into a predefined set of buckets, A1, A2, A3 and None.
I was wondering if a dynamic if-then-else could be constructed from the content before evaluating each transaction or if there is a more elegant option in Qlik to achieve this.
Any assistance is appreciated! Thanks
Shaheer Mecci.
An example of a set of rules is below in the table.
Bucket Name | Field Name | Criteria + Value |
---|---|---|
A1 | [Balance Amount] | >= 1,000 |
A1 | [Balance Amount] | <= 10,000 |
A1 | Category | = 'C' |
A2 | [Balance Amount] | >= 5,000 |
A2 | [Category | = 'B' |
A2 | [Sale Date] | >= '01/01/2017' |
A2 | [Sale Date] | <= '06/01/2017' |
A3 | [Balance Amount] | >= 1,000 |
A3 | [Sale Date] | >= '05/01/2017' |
Transaction data may look like below. Some rows meet multiple Bucket criteria (ex: Row 5).
Some rows may not meet any criteria and they need to be marked 'None'.
Key | [Balance Amount] | Category | [Sale Date] | State | Bucket A1 | Bucket A2 | Bucket A3 | Reason |
---|---|---|---|---|---|---|---|---|
1 | 2000 | C | 02/01/2017 | NJ | Yes | No | No | meets all conditions for A1 |
2 | 2500 | B | 06/01/2017 | NJ | No | No | Yes | meets A3 condition |
3 | 5000 | C | 12/01/2017 | FL | No | No | Yes | meets A3 condition |
4 | 12000 | B | 04/30/2017 | VA | No | Yes | No | meets A2 condition |
5 | 10000 | B | 05/01/2017 | NJ | No | Yes | Yes | meets A2 and A3 condition |
Ok, few adjustments and finally got it:
I hope this helps!
Cheers,
Luis
Criteria:
Load
Bucket,
Concat(FieldName & ' ' & Criteria, ' AND ') as Condition
Group by Bucket;
Load
"Bucket Name" as Bucket,
"Field Name" as FieldName,
"Criteria + Value" as Criteria
FROM [lib://AttachedFiles/Criterion.xlsx]
(ooxml, embedded labels, table is Sheet1);
TempTransactions:
Load
Key,
"[Balance Amount]" as [Balance Amount],
Category,
"[Sale Date]" as [Sale Date],
State
FROM [lib://AttachedFiles/Transactions.xlsx]
(ooxml, embedded labels, table is Sheet1);
// Evaluating conditions for each record, one criteria (bucket) at a time
Let vNumberOfBuckets = NoOfRows('Criteria') - 1;
For vCounter = 0 to $(vNumberOfBuckets)
Let vBucket = Peek('Bucket' ,vCounter,'Criteria');
Let vCondition = Peek('Condition',vCounter,'Criteria');
Let vBucketName = '[Bucket ' & vBucket &']';
[$(vBucket)]:
NoConcatenate
Load
Key,
IF($(vCondition),'Yes','No') as $(vBucketName)
Resident TempTransactions;
Next vCounter;
// Creating Reasons
For vCounter = 0 to $(vNumberOfBuckets)
TempReasons:
Let vBucket = Peek('Bucket',vCounter,'Criteria');
Let vBucketName = '[Bucket ' & vBucket &']';
Load
Key,
If($(vBucketName) = 'Yes','$(vBucket)') as Reason
Resident [$(vBucket)];
Next vCounter;
// Grouping Reasons
Reasons:
NoConcatenate
Load
Key,
'Buckets: ' & Concat(Distinct Reason,', ') as Reasons
Resident TempReasons
Group by Key;
// Joining the tables created:
Transactions:
NoConcatenate
Load * Resident TempTransactions;
For vCounter = 0 to $(vNumberOfBuckets)
Let vBucket = Peek('Bucket',vCounter,'Criteria');
Let vReasonName = '[Reason ' & vBucket &']';
Join Load * Resident [$(vBucket)];
Next vCounter;
Join Load * Resident Reasons;
// Dropping tables
Drop Table TempTransactions;
Drop Table TempReasons;
For vCounter = 0 to $(vNumberOfBuckets)
Let vBucket = Peek('Bucket',vCounter,'Criteria');
Drop Table [$(vBucket)];
Next vCounter;
Drop Table Reasons;
Hi Shaheer, this is an interesting one. I started id but I got stuck and I won't be able to come back to it until Monday.
The first thing I did was to concatenate all buckets into one table so I can apply one big IF($(Bucket)... and I did it with this:
Criteria:
LOAD
Bucket,
Concat(FieldName & ' ' & Criteria, ' AND ') as Condition
Group by Bucket;
LOAD
"Bucket Name" as Bucket,
"Field Name" as FieldName,
"Criteria + Value" as Criteria
FROM [lib://AttachedFiles/Criterion.xlsx]
(ooxml, embedded labels, table is Sheet1);
Transactions:
LOAD
Key,
"[Balance Amount]" as [Balance Amount],
Category,
"[Sale Date]" as [Sale Date],
State
FROM [lib://AttachedFiles/Transactions.xlsx]
(ooxml, embedded labels, table is Sheet1);
Then I did this to create a result for each bucket evaluation:
Let vNumberOfBuckets = NoOfRows('Criteria') - 1;
For vCounter = 0 to $(vNumberOfBuckets)
Let vBucket = Peek('Bucket' ,vCounter,'Criteria');
Let vCondition = Peek('Condition',vCounter,'Criteria');
$(vBucket):
NoConcatenate
Load
Key,
IF($(vCondition),'Yes','No') as $(vBucket)
Resident Transactions;
Next vCounter;
I got the evaluation right but now I have to put everything into one table
This will work but it's not elegant as ideally everything should be in one table,
Until next week,
Luis
Ps, I have to cleanup the Criteria table a little bit. This is what I used:
Ok, few adjustments and finally got it:
I hope this helps!
Cheers,
Luis
Criteria:
Load
Bucket,
Concat(FieldName & ' ' & Criteria, ' AND ') as Condition
Group by Bucket;
Load
"Bucket Name" as Bucket,
"Field Name" as FieldName,
"Criteria + Value" as Criteria
FROM [lib://AttachedFiles/Criterion.xlsx]
(ooxml, embedded labels, table is Sheet1);
TempTransactions:
Load
Key,
"[Balance Amount]" as [Balance Amount],
Category,
"[Sale Date]" as [Sale Date],
State
FROM [lib://AttachedFiles/Transactions.xlsx]
(ooxml, embedded labels, table is Sheet1);
// Evaluating conditions for each record, one criteria (bucket) at a time
Let vNumberOfBuckets = NoOfRows('Criteria') - 1;
For vCounter = 0 to $(vNumberOfBuckets)
Let vBucket = Peek('Bucket' ,vCounter,'Criteria');
Let vCondition = Peek('Condition',vCounter,'Criteria');
Let vBucketName = '[Bucket ' & vBucket &']';
[$(vBucket)]:
NoConcatenate
Load
Key,
IF($(vCondition),'Yes','No') as $(vBucketName)
Resident TempTransactions;
Next vCounter;
// Creating Reasons
For vCounter = 0 to $(vNumberOfBuckets)
TempReasons:
Let vBucket = Peek('Bucket',vCounter,'Criteria');
Let vBucketName = '[Bucket ' & vBucket &']';
Load
Key,
If($(vBucketName) = 'Yes','$(vBucket)') as Reason
Resident [$(vBucket)];
Next vCounter;
// Grouping Reasons
Reasons:
NoConcatenate
Load
Key,
'Buckets: ' & Concat(Distinct Reason,', ') as Reasons
Resident TempReasons
Group by Key;
// Joining the tables created:
Transactions:
NoConcatenate
Load * Resident TempTransactions;
For vCounter = 0 to $(vNumberOfBuckets)
Let vBucket = Peek('Bucket',vCounter,'Criteria');
Let vReasonName = '[Reason ' & vBucket &']';
Join Load * Resident [$(vBucket)];
Next vCounter;
Join Load * Resident Reasons;
// Dropping tables
Drop Table TempTransactions;
Drop Table TempReasons;
For vCounter = 0 to $(vNumberOfBuckets)
Let vBucket = Peek('Bucket',vCounter,'Criteria');
Drop Table [$(vBucket)];
Next vCounter;
Drop Table Reasons;
Luis,
Perfect! Just what I needed. Thank you!
Shaheer
Hi,
one generic solution might be:
tabRules:
LOAD * INLINE [
Bucket Name, Field Name, Criteria + Value
A1, Balance Amount, >= 1000
A1, Balance Amount, <= 10000
A1, Category, "= 'C'"
A2, Balance Amount, >= 5000
A2, Category, "= 'B'"
A2, Sale Date, ">= '01/01/2017'"
A2, Sale Date, "<= '06/01/2017'"
A3, Balance Amount, >= 1000
A3, Sale Date, ">= '05/01/2017'"
];
tabTransact:
LOAD * INLINE [
Key, Balance Amount, Category, Sale Date, State
1, 2000, C, 02/01/2017, NJ
2, 2500, B, 06/01/2017, NJ
3, 5000, C, 12/01/2017, FL
4, 12000, B, 04/30/2017, VA
5, 10000, B, 05/01/2017, NJ
];
tabTemp:
CrossTable ([Field Name],FieldValue)
LOAD * Resident tabTransact;
Inner Join (tabTemp)
LOAD * Resident tabRules;
tabTemp2:
Generic
LOAD Key,
'Bucket '&[Bucket Name],
If(Max(Evaluate(Chr(39)&FieldValue&Chr(39)&[Criteria + Value])),'Yes','No')
Resident tabTemp
Group By Key,[Bucket Name];
DROP Table tabTemp;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'tabTemp2.*') THEN
LEFT JOIN (tabTransact) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
hope this helps
regards
Marco
Please close your thread if your question is answered:
Qlik Community Tip: Marking Replies as Correct or Helpful
Thanks
Regards
Marco
Thanks Marco. Your solution helped too.
I have been trying to find the option to mark as answered and couldn't find one. I set Luis' response to Helpful. That is the only option in the Actions drop-down.