Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai33
Partner - Creator
Partner - Creator

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)
  • Peek

2 Solutions

Accepted Solutions
paulinhok14
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

sunny_talwar

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
Sai33
Partner - Creator
Partner - Creator
Author

Any expert has a quick suggestion 

@sunny_talwar 

paulinhok14
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;

sunny_talwar

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;
Sai33
Partner - Creator
Partner - Creator
Author

Thank you both. Works perfectly!