6 Replies Latest reply: Nov 9, 2017 10:14 PM by Shaheer Mecci RSS

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

    Shaheer Mecci

      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
        • Re: Dynamically evaluate set of rules to derive calculated field (in load script)
          Luis Madriz

          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

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

              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;

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

              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