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

In load-set blank cell to value above.

Mon, 01 Dec 2014 19:42:53 GMT

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?

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!

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

MarcoWedel — Tue, 02 Dec 2014 00:13:36 GMT

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

Otherfields

FROM source;

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

Gabriel — Tue, 02 Dec 2014 00:36:01 GMT

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

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

Tue, 02 Dec 2014 16:56:59 GMT

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.

MarcoWedel — Tue, 02 Dec 2014 17:07:13 GMT

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

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

simenkg — Tue, 02 Dec 2014 17:09:29 GMT

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.

MarcoWedel — Tue, 02 Dec 2014 17:15:02 GMT

But if IsNull() works, then simply

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

should work as well.

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

Tue, 02 Dec 2014 17:15:11 GMT

Thank you, works just fine now!

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

Tue, 02 Dec 2014 17:32:32 GMT

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!