In load-set blank cell to value above.

[#1]

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!

[MarcoWedel]

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

            Otherfields

FROM source;

[Gabriel · Accepted solution]

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

[#4]

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?

[MarcoWedel]

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

[simenkg]

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

[MarcoWedel]

But if IsNull() works, then simply

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

should work as well.

[#8]

Thank you, works just fine now!

[#9]

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!