Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

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!

4 Replies

Re: STATIC Function Help

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

regards

Marco

hector_munoz_in
Valued Contributor

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:

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

Not applicable

Re: STATIC Function Help

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-

hector_munoz_in
Valued Contributor

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

Community Browser