Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Luis_Calvo
Contributor III
Contributor III

FIND THE PREVIOUS RECORD WITH PEEK OR PREVIUS

Hi everyone,
I thank you in advance for all the possible help. I have the following dilemma:

I am trying to define a new field based on another but I have not succeeded. What I basically want is to keep in this field the last record of field1 in field2 until that record changes.
I enclose a table of what I want.

field1 field2
1111
-11
-11
-11
-11
1212
-12
-12
1313
-13
4545
-45
-45

I clarify that I want to do this in the backend inside a Load I remain attentive and thank you very much

 

 

 

 

 

 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
Luis_Calvo
Contributor III
Contributor III
Author

I was on the right track, the only thing is that I did not know that I could refer to a field that until now I was defining.

Thank you very much, this is what I was looking for.

View solution in original post

3 Replies
gavinlaird
Contributor III
Contributor III

The script below loads the "field1" value as the "field2" value when there is a value in "field1". Where there is no value in "field1", it loads the most recent "field2" value.

LOAD 
	field1
	,if(len(field1)>0,field1,Peek('field2')) as field2
FROM [your data];

 

Luis_Calvo
Contributor III
Contributor III
Author

I was on the right track, the only thing is that I did not know that I could refer to a field that until now I was defining.

Thank you very much, this is what I was looking for.

Vegar
MVP
MVP

The solution of @Luis_Calvo  will work and might even be better than mine below, but you should be aware that this possibility exsits.

If your source is Excel you can use the filter transformation supported by qlik. I have not fully managed to learn this syntax by heart so I tend to use the file wizard provided in the QlikView desktop when I need to manipulate data from Excel.

For a simple xlsx containing only your sample field1 it will look like this:

LOAD 
   field1, 
   field11 as field2 FROM
[Data.xlsx]
(ooxml, embedded labels, table is Blad2, filters(
   ColXtr(1, RowCnd(Every), 0),
   ColXtr(1, RowCnd(Every), 0), 
   Replace(3, top, StrCnd(null))
));

The output will look like this:

image.png