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

# STATIC Function Help

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

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

regards

Marco

• ###### Re: STATIC Function Help

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:
Account
Cash and Cash Equivalent
1000-Cash
100000
100004
100034
Accounts Receivable
2000-AR
200000
200009
];

T2:
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,

H

• ###### Re: STATIC Function Help

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)

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:

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

Hi Nina,

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

Regards,

H