FIND THE PREVIOUS RECORD WITH PEEK OR PREVIUS

[Luis_Calvo]

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
11	11
-	11
-	11
-	11
-	11
12	12
-	12
-	12
13	13
-	13
45	45
-	45
-	45

I clarify that I want to do this in the backend inside a Load

I remain attentive and thank you very much

[gavinlaird]

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 · Accepted solution]

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]

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: