Update rows with another by key and dates

[Antoine553]

Hello

I'm having a list a payment that i imported from Excel looking  a little bit like this:

ID	Name	Job	price	Date
1	John Doe	Magician	40	12/06/2019
2	Will Smith	Actor	32	11/05/2019
3	John Doe	Sorcerer	36	20/06/2019
4	Jack Sparrow	Pirate	22	32/13/5899
5	John Doe	-	12	10/07/2019

I'm displaying it in a straight table. Sometimes the job might be missing. How can i complete the empty "Job" field by the last registered job for John Doe (Sorcerer) pls ?

[PriyankaShivhare · Accepted solution]

Set NullInterpret = '';
Test:
Load ID,
Name,
Job,
price,
Date#(Date,'DD/MM/YYYY') as Date

inline [
ID ,Name ,Job ,price ,Date
1, John Doe, Magician, 40, 12/06/2019
2, Will Smith, Actor, 32 ,11/05/2019
3, John Doe, Sorcerer, 36, 20/06/2019
4, Jack Sparrow, Pirate, 22, 30/12/2015
5, John Doe ,, 12, 10/07/2019];

NoConcatenate
Test1:
Load ID,
Name,
Job,
price,
Date,
if(Name=peek(Name)and isnull(Job),peek(Job1),Job) as Job1
resident Test
order by Name,Date
;

drop table Test;

hope this helps

Thanks,

Priyanka Shivhare