0 Replies Latest reply: Sep 14, 2016 11:06 AM by Brian McNabb RSS

    Expression Character Limit Reached in Qliksense

    Brian McNabb

      I've ran into the issue where I have a large expression that has reached the character limit. I know there is a way to work around this by creating variables or adding the expression in the data load editor. However, my coding background is pretty limited and I've not been able to figure it out by trouble shooting myself. Here is the expression I'm trying to include in the load script along with the current load:

       

      Expression

      IF(fabs(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))

      /sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))<=.1,0,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))/

      sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))<=.25,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))/

      sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))>.10,1),

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))/

      sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))<=.75,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))/

      sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))>.25,3),

       

      IF(fabs(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))

      /sum({$<[Period Name]={'Q1-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))>.75,5,0))))+

       

      IF(fabs(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))

      /sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))<=.1,0,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))/

      sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))<=.25,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))/

      sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))>.10,1),

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))/

      sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))<=.75,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))/

      sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))>.25,3),

       

      IF(fabs(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))

      /sum({$<[Period Name]={'Q1-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))>.75,5,0))))+

       

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])<=10000000,0,

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])>10000000,

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])<=100000000,1,

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])>100000000,

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])<=500000000,3,

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])>500000000,

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])<=1000000000,5,

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])>1000000000,

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])<=5000000000,7,

      IF(sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])>5000000000,10,0))))))))))+

       

      IF(fabs(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))

      /sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))<=.1,0,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))/

      sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))<=.25,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))/

      sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))>.10,1),

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))/

      sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))<=.75,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))/

      sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))>.25,3),

       

      IF(fabs(((sum({$<[Period Name]={'Q2-16'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM]))

      /sum({$<[Period Name]={'Q2-15'},[Level 4 Account]={'A5000000'}>}[Ptd Balance SUM])))>.75,5,0))))+

       

      IF(fabs(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))

      /sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))<=.1,0,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))/

      sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))<=.25,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))/

      sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))>.10,1),

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))/

      sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))<=.75,

       

      IF(FABS(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))/

      sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))>.25,3),

       

      IF(fabs(((sum({$<[Period Name]={'Q2-16'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])

      -sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM]))

      /sum({$<[Period Name]={'Q2-15'},[Level 3 Account]={'A1000000'}>}[Ytd Balance SUM])))>.75,5,0))))+

       

      IF([CPI Index Gameboard]='Not Available',5,

      IF([CPI Index Gameboard]<=25,10,

      IF([CPI Index Gameboard]>25,IF([CPI Index Gameboard]<=35,9,

      IF([CPI Index Gameboard]>35,IF([CPI Index Gameboard]<=45,8,

      IF([CPI Index Gameboard]>45,IF([CPI Index Gameboard]<=55,5,

      IF([CPI Index Gameboard]>55,IF([CPI Index Gameboard]<=65,4,

      IF([CPI Index Gameboard]>65,IF([CPI Index Gameboard]<=75,3,

      IF([CPI Index Gameboard]>75,IF([CPI Index Gameboard]<=85,2,

      IF([CPI Index Gameboard]>85,IF([CPI Index Gameboard]<=90,1,

      IF([CPI Index Gameboard]>90,0)))))))))))))))))+

       

      IF(Count({$<[Audit Opinion]={'Satisfactory'}>}[Audit Name])>=4,0,

      IF(Count({$<[Audit Opinion]={'Satisfactory'}>}[Audit Name])<4,

      IF(Count({$<[Audit Opinion]={'Satisfactory'}>}[Audit Name])>0.1,1,0)))+

      IF(Count({$<[Audit Opinion]={'Needs Improvement'}>}[Audit Name])>0.1,5,0)+

      IF(Count({$<[Audit Opinion]={'Unacceptable'}>}[Audit Name])>0.1,10,0)+

      IF(Count({$<[Audit Opinion]={'Unacceptable','Needs Improvement','Satisfactory'}>}[Audit Name])<1,10,0)

       

      Load Script

      [Sheet1]:

      LOAD

      [Le],

        [Management Entity],

        [Account],

        [Account - Description],

        [Account Description],

        [Account GL Number],

        [Account Type],

        [Begin Balance SUM],

        [Bs Is],

        [Business],

        [Cash Flow Category],

        [Country],

        [Currency Code],

        [Gold id],

        [Interco Le],

        [Interco Me],

        [LE Description],

        [Level 1 Account],

        [Level 1 Account Description],

        [Level 2 Account],

        [Level 2 Account Description],

        [Level 3 Account],

        [Level 3 Account Description],

        [Level 4 Account],

        [Level 4 Account Description],

        [Level 5 Account],

        [Level 5 Account Description],

        [Level 6 Account],

        [Level 6 Account Description],

        [Level 7 Account],

        [Level 7 Account Description],

        [Level 8 Account],

        [Level 8 Account Description],

        [Level 9 Account],

        [Level 9 Account Description],

        [ME - Description],

        [ME Description],

        [Period Name],

        [Period Num],

        [Period Year],

        [Ptd Balance SUM],

        [Segment],

        [Sob Short Name],

        [Sub Business],

        [Type],

        [Value 1 Description],

        [Value 10 Description],

        [Value 11 Description],

        [Value 12 Description],

        [Value 13 Description],

        [Value 14 Description],

        [Value 2 Description],

        [Value 3 Description],

        [Value 4 Description],

        [Value 5 Description],

        [Value 6 Description],

        [Value 7 Description],

        [Value 8 Description],

        [Value 9 Description],

        [Value1],

        [Value10],

        [Value11],

        [Value12],

        [Value13],

        [Value14],

        [Value2],

        [Value3],

        [Value4],

        [Value5],

        [Value6],

        [Value7],

        [Value8],

        [Value9],

        [Ytd Balance SUM],

        [Gameboard Entity Number],

        [Gameboard Entity Description],

        [Gameboard Country],

        [CPI Index Gameboard],

        [Latitude Gameboard],

        [Longitude Gameboard],

        [Gameboard Entity/Business]

           FROM [lib://Alteryx - Capital Dashboard/Alteryx Capital MARS Financials.Assets FINAL2T16..xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      [Sheet1]:

      LOAD [Le],

        [Management Entity],

        [Account],

        [Account - Description],

        [Account Description],

        [Account GL Number],

        [Account Type],

        [Begin Balance SUM],

        [Bs Is],

        [Business],

        [Cash Flow Category],

        [Country],

        [Currency Code],

        [Gold id],

        [Interco Le],

        [Interco Me],

        [LE Description],

        [Level 1 Account],

        [Level 1 Account Description],

        [Level 2 Account],

        [Level 2 Account Description],

        [Level 3 Account],

        [Level 3 Account Description],

        [Level 4 Account],

        [Level 4 Account Description],

        [Level 5 Account],

        [Level 5 Account Description],

        [Level 6 Account],

        [Level 6 Account Description],

        [Level 7 Account],

        [Level 7 Account Description],

        [Level 8 Account],

        [Level 8 Account Description],

        [Level 9 Account],

        [Level 9 Account Description],

        [ME - Description],

        [ME Description],

        [Period Name],

        [Period Num],

        [Period Year],

        [Ptd Balance SUM],

        [Segment],

        [Sob Short Name],

        [Sub Business],

        [Type],

        [Value 1 Description],

        [Value 10 Description],

        [Value 11 Description],

        [Value 12 Description],

        [Value 13 Description],

        [Value 14 Description],

        [Value 2 Description],

        [Value 3 Description],

        [Value 4 Description],

        [Value 5 Description],

        [Value 6 Description],

        [Value 7 Description],

        [Value 8 Description],

        [Value 9 Description],

        [Value1],

        [Value10],

        [Value11],

        [Value12],

        [Value13],

        [Value14],

        [Value2],

        [Value3],

        [Value4],

        [Value5],

        [Value6],

        [Value7],

        [Value8],

        [Value9],

        [Ytd Balance SUM],

        [Gameboard Entity Number],

        [Gameboard Entity Description],

        [Gameboard Country],

        [CPI Index Gameboard],

        [Latitude Gameboard],

        [Longitude Gameboard],

        [Gameboard Entity/Business]

      FROM [lib://Alteryx - Capital Dashboard/Alteryx Capital MARS Financials.Income FINAL2T16..xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      I had to split the load files in two since they were too large for excel to handle.

       

      Any help would be greatly appreciated!

       

      Thanks,

      Brian