Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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 (3)
1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

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
Highlighted
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];

 

Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes