topic Re: Autocomplete empty records with a previous value in QlikView

Autocomplete empty records with a previous value

Tooomooo — Wed, 25 Nov 2020 16:16:04 GMT

HELO

I have a little problem. I have two views: Master Calendar and Temp

LET vMinDate = Num(MakeDate(2020,9,18));

LET vMaxDate = Num(Today()-1);

LET vDays = vMaxDate - vMinDate + 2;

Master Calendar:

LOAD

date(floor(TimeStamp,0.000694444446708076),'YYYY-MM-DD hh:mm:ss')as CreationDate

;
LOAD

Timestamp($(vMinDate) + (RecNo() - 1)/1440) as TimeStamp

AUTOGENERATE 1440 * $(vDays);

Temp:

LOAD * INLINE [
     CreationDate, Stan
    2020-09-18 00:07:12, A
    2020-09-18 00:10:12, B
    2020-09-18 00:15:12, C
    2020-09-18 00:18:12, D
    2020-09-18 05:22:12, A
    2020-09-18 05:25:12, B
    2020-09-18 08:32:12, C
    2020-09-18 08:34:12, D
    2020-09-18 07:12:00, A
    2020-09-18 08:42:12, B
];

After reloading the script, I have tables

CreationDate	Stan
2020-09-17 23:59:12
2020-09-18 00:00:12
2020-09-18 00:01:12
2020-09-18 00:02:12
2020-09-18 00:03:12
2020-09-18 00:04:12
2020-09-18 00:05:12
2020-09-18 00:06:12
2020-09-18 00:07:12	A
2020-09-18 00:07:12
2020-09-18 00:08:12
2020-09-18 00:09:12
2020-09-18 00:10:12	B
2020-09-18 00:10:12
2020-09-18 00:11:12
2020-09-18 00:12:12
2020-09-18 00:13:12
2020-09-18 00:14:12
2020-09-18 00:15:12	C
2020-09-18 00:15:12
2020-09-18 00:16:12
2020-09-18 00:17:12
2020-09-18 00:18:12	D
2020-09-18 00:18:12

How to fill in empty fields in a table with an earlier value. I suppose you have to use the privus function.
But how ???

Re: Autocomplete empty records with a previous value

rubenmarin — Sun, 20 Sep 2020 09:07:05 GMT

Hi, I've already answered a very similar question here: https://community.qlik.com/t5/New-to-QlikView/Creating-a-master-calendar-and-filling-in-missing-data/td-p/1745372

You can use Peek() or Previous() to retrieve the value of the previous record, and use a sorted load to load them by date ("Order by CreationDate" or "Order By CreationDate desc" depending if you want to propagate them forwards or backwards, something like:

Outer Join (Temp) LOAD CreationDate Resident MasterCalendar; TempFilled: LOAD CreationDate, If(IsNull(Stan), Peek(Stan), Stan) as StanRICE Resident Temp Order By CreationDate; DROP Table Temp;

Re: Autocomplete empty records with a previous value

Tooomooo — Sun, 20 Sep 2020 16:31:20 GMT

After adding the code and reloading, I saw a view of duplicate values. There are still empty values in the columns StanRICE

Re: Autocomplete empty records with a previous value

Brett_Bleess — Wed, 14 Oct 2020 15:15:49 GMT

@Tooomooo It may be most helpful if you can attach a QVW we can look at directly and tweak and reattach at this point to ensure you get the right solution. @rubenmarin can you have a second look just to see if you may be able to sort something without the QVW file?

Cheers,
Brett

Re: Autocomplete empty records with a previous value

rubenmarin — Wed, 14 Oct 2020 15:23:02 GMT

Hi @Tooomooo @Brett_Bleess , probably it needs to be

If(IsNull(Stan), Peek(StanRICE), Stan) as StanRICE

Re: Autocomplete empty records with a previous value

Chanty4u — Wed, 14 Oct 2020 15:51:05 GMT

can you try this?

if(len(trim(StanRICE))=0,Peek('StanRICE'),StanRICE) as StanRICE

Use Previous function:

IF(LEN(StanRICE)=0,PREVIOUS(StanRICE),StanRICE) AS StanRICE