Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

In load-set blank cell to value above.

So, I have a report that is separated by day, but it doesn't repeat the day on each row. It lists it at the first occurrence and then lists all the information below it. Is there a way to load it into qlikview where if it sees the blank row, fills it with the first found value above it?

Capture.JPG.jpg

As you can see, the date is listed once, but I need all the values below it to be associated with that date. Thank you advance for the help!

Tags (2)
1 Solution

Accepted Solutions
Gabriel
Valued Contributor II

Re: In load-set blank cell to value above.

Hi Weston,

It is possible to return value above current row if current row is blank or Null.

There are 3 function that we can use here (potentially more).

These functions are

ISNULL()

LEN()

PEEK()

Try these script line

,IF(LEN(BlankFieldName)=0,Peek('BlankFieldName'),BlankFieldName)           AS Whatever

If this doesn't work then substitute LEN() with ISNULL(). As in

,IF(ISNULL(IsNullFieldName),Peek('IsNullFieldName'),IsNullFieldName)       AS Whatever

Hope this help

Best Regards,

Gabriel

8 Replies

Re: In load-set blank cell to value above.

LOAD Alt(Date, Peek(Date)) as Date,

            Otherfields

FROM source;

Gabriel
Valued Contributor II

Re: In load-set blank cell to value above.

Hi Weston,

It is possible to return value above current row if current row is blank or Null.

There are 3 function that we can use here (potentially more).

These functions are

ISNULL()

LEN()

PEEK()

Try these script line

,IF(LEN(BlankFieldName)=0,Peek('BlankFieldName'),BlankFieldName)           AS Whatever

If this doesn't work then substitute LEN() with ISNULL(). As in

,IF(ISNULL(IsNullFieldName),Peek('IsNullFieldName'),IsNullFieldName)       AS Whatever

Hope this help

Best Regards,

Gabriel

Not applicable

Re: In load-set blank cell to value above.

I followed your format:

If(ISNull([Agent Name]),UPPER(Peek('Agent Name')),UPPER([Agent Name])) As CSR_NAME

but it is not loading CSR_NAME correctly.  For some reason it loads correctly if I don't rename the field and just load it ias [Agent Name]. But trying to rename has it load the same way as before. Any thoughts?

Re: In load-set blank cell to value above.

If(ISNull([Agent Name]),UPPER(Peek(CSR_NAME)),UPPER([Agent Name])) As CSR_NAME

bwisenosimenkg
Valued Contributor

Re: In load-set blank cell to value above.

This is because Peek looks at the output of the last line.

Since Agent Name is not set again it will not exists for any other line than the first.

The fix is to either Peek('Agent Name') and not rename the field or Peek('CSR_Name') and rename the field to CSR_NAME

If(ISNull([Agent Name]),UPPER(Peek('CSR_NAME')),UPPER([Agent Name])) As CSR_NAME



Re: Re: In load-set blank cell to value above.

But if IsNull() works, then simply

Alt(UPPER([Agent Name]), Peek(CSR_NAME)) As CSR_NAME


should work as well.

Not applicable

Re: In load-set blank cell to value above.

Thank you, works just fine now!

Not applicable

Re: In load-set blank cell to value above.

Another fun question!!! So, as you can see, the date is in YYYY.M.D, so I have been using MakeDate function and doing Left(Peek('Date'),4)...etc...not working. Can I use something else? Sorry, I just took training so I am still learning a lot!

Community Browser