Dynamically evaluate set of rules to derive calculated field (in load script)

[shaheermecci]

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

[luismadriz]

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:

[luismadriz · Accepted solution]

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;

[shaheermecci]

Luis,

Perfect! Just what I needed. Thank you!

Shaheer

[MarcoWedel]

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

[MarcoWedel]

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks

Regards

Marco

[shaheermecci]

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.