Need to lookup last used value

[samohtfrd]

Dear all,

I have a run into a problem which I would like to ask for some assistance:

My query looks like this:

Index          Article          Model           New column  

1                  A                    F1

2                  B                    F2

3                  C

4                  D

5                  E                    F3

6                  F                    F4

In my new column i would like to add the model. For each line with a blank field in column "Model" I would like to add the most recently used model. The resulting column should look like this:

New Column

F1

F2

F2

F2

F3

F4 

Can anyone help with this? Thanks!

[BhaveshPatel]

Hi There,

It is pretty simple and one step process. 

In the Query Editor, Go to Transform Tab and select Fill, Fill Down.

This will solve your Problem.

Blue Circled Section for your reference steps to follow in Query Editor

Thanks & Regards,

Bhavesh

[samohtfrd]

Dear Bhavesh,

I tried your solution. At first I tried in my original dataset (the example in my post was generalized because of confidential nature of original data), however this did ot yield nay result (the option fill down/up didn't do anything). Then I replicated you printscreen and tried again without any succes:

Can you see what I am doing wrong?

Thanks! 

[Dave2 · Accepted solution]

@samohtfrd your problem is that the blank values are actually empty strings, they need to be null for FillDown to work.  (That is, an empty string is still considered a value.)

Before you do the FillDown, right click the column and choose "Replace Values".  Leave "Values To Find" blank, and under "Replace With" put the word null

[samohtfrd]

Thanks for the help! problem solved!

[BhaveshPatel]

Hi There,

What do you mean by most recently used?

[samohtfrd]

By most recently used i mean the last value in the column "model".

The goal is to, in case of a blank field in "Model", make a formula search for the value in the line above. If that one is empty, search another line above etc. until it finds a value.

I tried using LASTNONBLANK but this formula doesn't seem to give the desired results.