Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Peek Fills wrong data

Hello All,

I have a requirement to generate missing values based on the existing values.

I've used Peek Function to generate the values but i don't see the correct values in the generated value list.

Data:

PIDF2 DateC Link IDLife TimeStatus
110.09.2019318.09.20195500
2  19.09.2019 
219.09.2019220.09.20196000
322.09.2019824.09.20198000
3  25.09.2019 

 

Expected Result:

PIDF2 DateC Link IDLife TimeStatus
110.09.2019318.09.20195500
2  19.09.2019 
219.09.2019220.09.20196000
322.09.2019824.09.20198000
322.09.2019825.09.20198000

 

Result i Get after using Peek:

PIDF2 DateC Link IDLife TimeStatus
110.09.2019318.09.20195500
210.09.2019319.09.20195500
219.09.2019220.09.20196000
322.09.2019824.09.20198000
322.09.2019825.09.20198000

 

The underlined part in the above table is completely wrong as PID = 2 has C Link ID=2 and F2 Date as 19.09.2019.

But because of Peek function i see that the values from PID=1 are filled for missing data in PID=2

 

PFA, sample app and also data.

 

Thanks!

Labels (1)
2 Solutions

Accepted Solutions
Highlighted
Creator
Creator

You need to create a conditional verifying the current PID and the last PID, for example:

If ( PID = Peek ( PID ), Peek ( [F2 Date] ) ) as YourNewField

Resident YourTable

Order By PID asc;

View solution in original post

Highlighted

Try this

Peek_Tmp:
LOAD
    PID,
    "F2 Date",
    Status,
    "C Link ID",
    "Life Time"
FROM [lib://D/Peek.xlsx]
(ooxml, embedded labels, table is Sheet1);


NoConcatenate
Peek:
LOAD
	PID,
    "Life Time",
    If(PID = Previous(PID),	If(IsNull([F2 Date]), Peek([F2 Date]), [F2 Date]), [F2 Date]) AS [F2 Date],
    If(PID = Previous(PID), If(IsNull([Status]), Peek([Status]), [Status]), [Status]) AS [Status],
    If(PID = Previous(PID), If(IsNull([C Link ID]), Peek([C Link ID]), [C Link ID]), [C Link ID]) AS [C Link ID]
Resident Peek_Tmp
Order By PID;

Drop Table Peek_Tmp;

View solution in original post

4 Replies
Highlighted
Partner
Partner

Any expert has a quick suggestion 

@sunny_talwar 

Highlighted
Creator
Creator

You need to create a conditional verifying the current PID and the last PID, for example:

If ( PID = Peek ( PID ), Peek ( [F2 Date] ) ) as YourNewField

Resident YourTable

Order By PID asc;

View solution in original post

Highlighted

Try this

Peek_Tmp:
LOAD
    PID,
    "F2 Date",
    Status,
    "C Link ID",
    "Life Time"
FROM [lib://D/Peek.xlsx]
(ooxml, embedded labels, table is Sheet1);


NoConcatenate
Peek:
LOAD
	PID,
    "Life Time",
    If(PID = Previous(PID),	If(IsNull([F2 Date]), Peek([F2 Date]), [F2 Date]), [F2 Date]) AS [F2 Date],
    If(PID = Previous(PID), If(IsNull([Status]), Peek([Status]), [Status]), [Status]) AS [Status],
    If(PID = Previous(PID), If(IsNull([C Link ID]), Peek([C Link ID]), [C Link ID]), [C Link ID]) AS [C Link ID]
Resident Peek_Tmp
Order By PID;

Drop Table Peek_Tmp;

View solution in original post

Highlighted
Partner
Partner

Thank you both. Works perfectly!