0 Replies Latest reply: Apr 2, 2017 1:59 PM by Ranajit Mehroke RSS

    Pivot table expression in load script

    Ranajit Mehroke

      The formula below is being used in a pivot table. I think it is the main reason for 'exceeded allocated memory' and 'object out of memory'.

      I want to implement this expression in the script. vR1 and vStoreRole are the variables being used in the formula.

      SignFlag gives 0 if it is null and 1 if it is not

       

       

      Table used for Role and RoleID. Multiple 'Role' are selected from the listbox using checkboxes to be used in the formula

       

       

      Role RoleID

      Manager 1

      Specialist 2

      Partner 3

      Staff 4

       

       

       

       

       

       

      = If(ISNULL

       

       

      (Num(Aggr(Count({<RoleID = {"$(vR1)","$(vR2)","$(vR3)","$(vR4)"}>} Distinct SubphaseReference), EngagementKey)

      /

      Aggr(Count({$<SignoffFlag -={""}>} Distinct SubphaseReference),EngagementKey) *100,0) )

       

       

      =-1, 0,

       

       

      Num(Aggr(Count({<RoleID = {"$(vR1)","$(vR2)","$(vR3)","$(vR4)"}>} Distinct SubphaseReference), EngagementKey)

      /

      Aggr(Count({$<SignFlag -={""}>} Distinct SubphaseReference),EngagementKey)

      *100,0)

      )

       

       

      vR1=SubField(vStoreRole,',',1)

       

       

      vR2=SubField(vStoreRole,',',2)

       

       

      vR3=SubField(vStoreRole,',',3)

       

       

      vR4=SubField(vStoreRole,',',4)

       

       

      vStoreRole=Concat(Distinct RoleID,',')