4 Replies Latest reply: Mar 30, 2017 12:00 AM by Muñoz Héctor RSS

    STATIC Function Help

    Nina Royce

      Hi Qlik Gurus,

       

      I need help in creating some additional fields and dragging information down (like a STATIC function in ACL). Please let me know if there is an option to that directly in Qlik.

       

      Help.PNG

       

      Here is similar logic for SQL

      sql - Auto fill field with value from above row value - Stack Overflow

       

      Any help is greatly appreciated!

        • Re: STATIC Function Help
          Marco Wedel

          You could use the Peek() function to achieve the required result.

           

          regards

           

          Marco

          • Re: STATIC Function Help
            Muñoz Héctor

            Hi Nina,

             

            With this code you could reach what you need. The precondition is T1 table has to be ordered properly in order to Peek() function invocations can recover the suitable value of the precedet records:

             

            T1:
            LOAD * INLINE [
            Account
            Cash and Cash Equivalent
            1000-Cash
            100000
            100004
            100034
            Accounts Receivable
            2000-AR
            200000
            200009
            ];
            
            T2:
            LOAD     Account                                   AS Account, 
                     If(IsNum(Left(Account, 4)),
                        If(IsNum(Left(Account, 6)), 
                           Peek('4-digit Account', -1, 'T2'), 
                           Account), 
                        Null())                                AS [4-digit Account], 
                      If(IsNum(Left(Account, 4)),
                         If(IsNum(Left(Account, 6)), 
                            Peek('Account Roll Up', -1, 'T2'), 
                            Peek('Account', -1, 'T2')), 
                         Null())                               AS [Account Roll Up]
            RESIDENT T1;
            
            DROP TABLE T1;
            

             

            29-03-2017 3-46-46.png

             

            I attach you a sample, anyway... Hope it serves!

             

            Regards,

            H

              • Re: STATIC Function Help
                Nina Royce

                Hi Hector,

                 

                Thank you for yur help. I was able to get through the first step. Screenshot below.

                 

                question.png

                 

                the code is

                 

                T1:

                Directory;

                CrossTable([BU], F1, 2)

                LOAD

                RECNO() AS [Record Number],

                 

                // 10k account roll-up (two examples for now)

                 

                IF(F1 = 'Cash_Cash_Equiv - Cash and Cash Equiv','10k - Cash and Cash Equivalents',

                IF(F1 = 'ST_Investments - Short term Investments (91 days to a year)','10k - Short-term Investments',F1)) AS Account,

                *

                 

                FROM

                [Excel File.xlsx]

                (ooxml, embedded labels, table is [Balance Sheet], filters(

                Remove(Row, Pos(Top, 11)),

                Remove(Row, Pos(Top, 10)),

                Remove(Row, Pos(Top, 9)),

                Remove(Row, Pos(Top, 7)),

                Remove(Row, Pos(Top, 6)),

                Remove(Row, Pos(Top, 5)),

                Remove(Row, Pos(Top, 4)),

                Remove(Row, Pos(Top, 3)),

                Remove(Row, Pos(Top, 2)),

                Remove(Row, Pos(Top, 1))

                ));

                 

                 

                T2:

                LOAD [Record Number],

                Account AS Account,

                 

                IF(IsNum(Left(Account,4)),

                If(IsNum(Left(Account,8)),

                Peek('4-digit Account', -1, 'T2'),

                Account),

                Null()) AS [4-digit Account],

                 

                          If(Left(Account,3) = '10k',   

                                Peek('10k Rollup', -1, 'T2'),  

                                Peek('Account', -1, 'T2')),  

                             Null() AS [10k Rollup]

                 

                RESIDENT T1 ORDER BY [Record Number];

                 

                DROP TABLE T1;

                 

                 

                 

                However, I still need to populate 10k rollup column with lines from account field that have 10-k in fron of it, for example for all cash accounts in 4-digit account filed i need to have '10k-Cash and Cash Equivalents" in the 10k Rollup field. Could you please advise how that can be done?

                 

                Thank you-