Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
LOAD Alt(Date, Peek(Date)) as Date,
Otherfields
FROM source;
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
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?
If(ISNull([Agent Name]),UPPER(Peek(CSR_NAME)),UPPER([Agent Name])) As CSR_NAME
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
But if IsNull() works, then simply
Alt(UPPER([Agent Name]), Peek(CSR_NAME)) As CSR_NAME
should work as well.
Thank you, works just fine now!
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!