Last non empty value in pivot table

[#1]

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

[tresesco]

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?

[#3]

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.

[#4]

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.

[#5]

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

[#6]

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