topic Last non empty value in pivot table in QlikView

Last non empty value in pivot table

Tue, 07 Jan 2014 05:24:20 GMT

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

Re: Last non empty value in pivot table

tresB — Tue, 07 Jan 2014 05:44:17 GMT

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?

Re: Last non empty value in pivot table

Tue, 07 Jan 2014 13:24:37 GMT

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.

Re: Last non empty value in pivot table

Wed, 08 Jan 2014 05:57:31 GMT

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.

Re: Last non empty value in pivot table

Wed, 08 Jan 2014 06:01:25 GMT

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

Re: Last non empty value in pivot table

Wed, 08 Oct 2014 12:21:02 GMT

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