topic Re: STATIC Function Help in QlikView

STATIC Function Help

Tue, 28 Mar 2017 21:50:15 GMT

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.

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

MarcoWedel — Tue, 28 Mar 2017 22:26:20 GMT

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

regards

Marco

Re: STATIC Function Help

hector_munoz — Wed, 29 Mar 2017 01:47:23 GMT

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;

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

Regards,

Re: STATIC Function Help

Wed, 29 Mar 2017 16:11:58 GMT

Hi Hector,

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

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-

Re: STATIC Function Help

hector_munoz — Thu, 30 Mar 2017 04:00:59 GMT

Hi Nina,

I am afraid I donot know how to help you unless you attach a sample of the original files... Could you?

Regards,