Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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,
H
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-