Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
przemyslaw_r
Contributor II
Contributor II

Missing data gaps

Hello All,

I have fallowing problem.

I need to fill missing data in the gaps, please check picture:

Capture.PNG

In my case before 1-2-2018 stock level needs to be 0 and after 1-2-2018 must be 19 until another stock level change.

I got this script, but it look that peek() function is not fetching 19 from 1-2-2018.

Stock:

LOAD

    SHORTL62,

    %REPORT_DATE,

    X08BAL

FROM [lib://2_Temp/BALANCE_TEMP_2.qvd]

(qvd);

final:

NoConcatenate

load

    SHORTL62,

    %REPORT_DATE,

    if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek(X08BAL),X08BAL),0) as [Stock level]

resident Stock

order by SHORTL62,%REPORT_DATE;

drop table Stock;

Could you advise what needs to be done?

1 Solution

Accepted Solutions
rubenmarin

Ok, you already have all dates and you only want to pass values... in that case your original script is failing because peek should use the field created in the same table, that is:

final:

NoConcatenate

load

    SHORTL62,

    %REPORT_DATE,

    if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek([Stock level]),X08BAL),0) as [Stock level]

resident Stock

order by SHORTL62,%REPORT_DATE;

drop table Stock;

View solution in original post

9 Replies
petter
MVP
MVP

Peek function needs to have the field name in single quotation marks:

Peek('X08BAL')

przemyslaw_r
Contributor II
Contributor II
Author

Thank you for answer!

I changed peek function as you mentioned, but is the same result.

Stock:

LOAD

    SHORTL62,

    %REPORT_DATE,

    X08BAL

FROM [lib://2_Temp/BALANCE_TEMP_2.qvd]

(qvd);

final:

NoConcatenate

load

    SHORTL62,

    %REPORT_DATE,

    if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek('X08BAL'),X08BAL),0) as [Stock level]

resident Stock

order by SHORTL62,%REPORT_DATE;

drop table Stock;

petter
MVP
MVP

Yes - the quotation marks can be used but are not necessary actually....

I think your problem is that you have to tell which table you are peeking from when you don't include the field in the last table:

Peek( X08BAL , RecNo()-1, 'Stock' )

przemyslaw_r
Contributor II
Contributor II
Author

I still have the gaps.

My Script:

Stock:

LOAD

    SHORTL62,

    %REPORT_DATE,

    X08BAL

FROM [lib://2_Temp/BALANCE_TEMP_2.qvd]

(qvd);

final:

NoConcatenate

load

    SHORTL62,

    %REPORT_DATE,

    if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek('X08BAL',RecNo()-1,'Stock'),X08BAL),0) as [Stock level]

resident Stock

order by SHORTL62,%REPORT_DATE;

drop table Stock;

STORE final INTO 'LIB://2_Temp//BALANCE_Final.qvd' (qvd);

petter
MVP
MVP

Well ... this should work:

final:

NoConcatenate

load

    SHORTL62,

    %REPORT_DATE,

    if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0 or IsNull(X08BAL),peek('Stock level'),Peek(X08BAL,RecNo()-1,'Stock')),0) as [Stock level]

resident Stock

order by SHORTL62,%REPORT_DATE;

rubenmarin

Hi, in two steps, one to add the end date of the range (in this sample I used today()), and another step to create the extra records, value is only filled for first record but can be easily passed to other records.

tmpLastDate:

LOAD

SHORTL62,

%REPORT_DATE,

If(SHORTL62=Peek(SHORTL62), Peek(%REPORT_DATE), Date(Today())) as lastDate, // fill from the last record until today

[Stock level]

resident tmpData

Order by SHORTL62, %REPORT_DATE desc

;

filledData:

NoConcatenate LOAD

SHORTL62,

%REPORT_DATE+IterNo()-1 as %REPORT_DATE,

If(IterNo()=1, [Stock level]) as [Stock level]

Resident tmpLastDate

While %REPORT_DATE + IterNo()-1 < lastDate;

DROP Table tmpData;

DROP Table tmpLastDate;

przemyslaw_r
Contributor II
Contributor II
Author

Thank you both for help.

The data is still missing. I have no idea what might be the problem.

I attached qvd with selected two products, maybe you can find error there.

Thanks!

rubenmarin

Ok, you already have all dates and you only want to pass values... in that case your original script is failing because peek should use the field created in the same table, that is:

final:

NoConcatenate

load

    SHORTL62,

    %REPORT_DATE,

    if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek([Stock level]),X08BAL),0) as [Stock level]

resident Stock

order by SHORTL62,%REPORT_DATE;

drop table Stock;

View solution in original post

przemyslaw_r
Contributor II
Contributor II
Author

Thank you!

This is correct solution.