Why Peek() is not populating the entire column?

[Anonymous]

Hi community,

Can anyone please tell me how to use the Peek() function properly so the all the missing values are populated by the above record's value. This is the output which is not what I wish to have

The sample data is attached and here's my script:

LOAD

    ID,

    Status,

    "Week",

    If(ID = Peek(ID) AND Len(Status) =0, Previous(Status), Status) As newStatus

FROM [lib://AttachedFiles/peeksample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Thanks,

[Gysbert_Wassenaar]

Possibly because Status isn't empty and len(Status) is not 0. Try Trim(Len(Status)) instead to get rid of any spaces.

Or your source data isn't ordered chronically by week.

[jonathandienst]

Your script will only backfill one entry.

Consider Gysbert's comments (esp about the chronological order), and change the script:

LOAD

    ID,

    Status as OrigStatus,  \\ just for validation purposes. can remove this line

    Week,

    If(ID = Previous(ID) And Len(Trim(Status)) = 0, Peek(Status), Status) As Status

FROM [lib://AttachedFiles/peeksample.xlsx]

(ooxml, embedded labels, table is Sheet1);

This will backfill any number of blank entries providing the data is properly sorted.

[Ralf_Heukäufer]

LOAD

    ID,

    Status,

    "Week",

    If(ID = Peek(ID) AND Len(Status) =0, Previous(Status), Status) As newStatus

Resident yourtablenamehere

order by ID, Week asc;

[Gysbert_Wassenaar]

Order by only works on resident loads.

[Ralf_Heukäufer]

Thanks for the note I have corrected it.

[Anonymous]

Thanks for your comments everyone,

@Jonathan,

Your solution is doing the job with the sample data , however with my actual data it still struggles to populate the entire records.

So, my table is a resident load and I use Order by on the ID and Week columns. I have a feeling that the week field is causing the issue and does't allow proper ordering. Let me try and fix that first and will get back to your solution.

Thanks again