Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

shaheermecci
New Contributor II

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

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 NameField NameCriteria + Value
A1[Balance Amount]>= 1,000
A1[Balance Amount]<= 10,000
A1Category= '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]StateBucket A1Bucket A2Bucket A3Reason
12000C02/01/2017NJYesNoNomeets all conditions for A1
22500B06/01/2017NJNoNoYesmeets A3 condition
35000C12/01/2017FLNoNoYesmeets A3 condition
412000B04/30/2017VANoYesNomeets A2 condition
510000B05/01/2017NJNoYesYesmeets A2 and A3 condition
1 Solution

Accepted Solutions
luismadriz
Valued Contributor

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

Ok, few adjustments and finally got it:

Untitled.png

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;

6 Replies
luismadriz
Valued Contributor

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

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

Untitled.png

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:

Untitled.png

luismadriz
Valued Contributor

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

Ok, few adjustments and finally got it:

Untitled.png

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
New Contributor II

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

Luis,

Perfect! Just what I needed. Thank you!

Shaheer

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

Hi,

one generic solution might be:

QlikCommunity_Thread_280309_Pic1.JPG

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

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

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks

Regards

Marco

shaheermecci
New Contributor II

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

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.

Community Browser