Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

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;

przemyslaw_r
Contributor II
Contributor II
Author

Thank you!

This is correct solution.