5 Replies Latest reply: Jun 24, 2013 9:22 PM by Abdul Mohammed RSS

    Replacing simple IF condition with Set Analysis

    Abdul Mohammed

      I have 2 tables both are not connected.

       

      Table 1

      F1F2
      A1
      B2
      C3
      D4

       

      Table 2

      F3F4
      A5
      B6
      C7
      D8

       

      I Created a straigt table with the dimention F3 and expression Sum( If(F3=F1,F2)). I get the following output.

       

      Output

      F3F2
      A1
      B2
      C3
      D4

       

      How do I replace this IF condition with a set analysis, Please advice

        • Re: Replacing simple IF condition with Set Analysis
          Abdul Mohammed

          I have a very complicated datamodel, There are reasons i cannot connect them in the data model.

           

          This If condition gives me correct results but it takes 100 times the memory of the file to calculate. If I remove the If condition the results are quick but wrong.

           

          Set Analysis allows us to calculate very complex logic, but there is no alternate for this simple logic.

            • Re: Replacing simple IF condition with Set Analysis
              Some Nath Roy

              Hi,

               

              You can fetch F2 value of having F1=F3 in script instead of expression. It will have faster user experience at UI.

              The below code snipets can be used:

               

              A:

              LOAD * Inline

              [F1,          F2

              A,          1

              B,          2

              C,          3

              D,          4];

               

               

              B:

              LOAD * Inline

              [F3,          F4

              A,          5

              B,          6

              C,          7

              D,          8];

               

              C:

              LOAD *, Lookup('F2','F1',F3,'A') as F5 Resident B;

               

               

              DROP Table B;

               

               

              Regards,

              Som

            • Re: Replacing simple IF condition with Set Analysis
              jagan mohan rao appala

               

              Hi Abdul,

               

              In your data model while loading table2 use mapping load to arrive a new column with F2 values, so that in chart you can straight get the values very quickly.

               

              Hope this helps you.

               

              Regards,

              Jagan.

              • Re: Replacing simple IF condition with Set Analysis
                Abdul Mohammed

                Thanks all for the answers, I was looking for only front end resolution, because both my tables are at different levels. Following is the resolution which worked for me.

                 

                A:

                F1F2A_FAA_FBA_FCA_FD
                A11
                B21
                C31
                D41

                 

                B:

                F3F4B_FAB_FBB_FCB_FD
                A51
                B61
                C71
                D81

                 

                Front end :

                Dimention = F3

                Actual Exp = Sum( If(F3=F1,F2))

                Expression = Alt( 

                                           Sum({<A_FA={1},B_FA=>} F2),

                                           Sum({<A_FB={1},B_FB=>} F2),

                                           Sum({<A_FC={1},B_FC=>} F2),

                                           Sum({<A_FD={1},B_FD=>} F2)

                                         )

                 

                Though the expression looks big and complicated, it is 100 times faster then the actual expression.