Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Last non empty value in pivot table

Hi All,

. I have a requirement to replace nulls with previous not null value.  Can you  suggest a solution with which we can achieve
the same

Options I tried.

1. Above
function: One level is working. Failing with multiple nulls

2. Peek
function in script level. Fact is having 100M records. Outer join will not be a good solution

Below link will give a clear idea on requirement

http://community.qlik.com/docs/DOC-1304

Thanks in Advance

Veena

5 Replies
MVP
MVP

Re: Last non empty value in pivot table

Why outer join? The link you provided per se shows a good way without having to use ouetr join I beleive. Am I missing something?

Not applicable

Re: Last non empty value in pivot table

In the above case, the best option is handle in the script by using Peek function. I believe we don't have other choice in the script.

Suppose if you have 100M rows fact table, you should implement the Incremental load. So while loading the data from DB table, you can apply your logic on the small data set and store into QVD.

Not applicable

Re: Last non empty value in pivot table

Hi

Thanks for pointing it out. it is  a start schema model. Already fact event has 100M records. Joining with dimension and peek will not be a good solution.

Not applicable

Re: Last non empty value in pivot table

Hi All

Thanks for the responses.

Below expression solved my issue.

Set [Exp] = IF(ISNULL(MAX(Field)),Above(([Exp])),MAX( Field))

With Best Regards

Veena

Not applicable

Re: Last non empty value in pivot table

Tried this, but for me it just prints the formula if I max(field) is null (comes into the recursive part). If not null, it works fine. I entered following in load script:

Set [Exp] = "IF(isnull(Max(Diff)) or max(diff) = 0,After(([Exp])),Max(Diff))";

And in expression in pivot table I wrote: =$(Exp)

Where did I go wrong?

Regards,

Tom Arne

Community Browser