Joining two tables in order to make peek

[annabergendal]

Hopefully someone can help me out.

I want to join two tables, but don't know how to get the result I want. The tables look something like this:

TableA

Datum

2013-01

2013-02

2013-03

...

2014-05

TableB

Datum          Quantity          Material

2013-03        10                   x  

2013-03        15                   y  

2013-06        5                    x 

2013-06        2                    y

I want a joined table like:

JoinedTable

Datum          Quantity          Material

2013-01        -                      x

2013-02        -                      x

2013-03        10                   x  

2013-04        -                      x

2013-05        -                      x

2013-06        5                     x

2013-01        -                      y

2013-02        -                      y

2013-03        15                   y

2013-04        -                      y

2013-05        -                      y

2013-06        2                     y

The reason I want to join like this is because I want to fill in the holes later with peek, so if NULL fill in the latest valid value... 

Can anyone advice me how to proceed with this? Can I even DO this?? Thanks!

[maxgro]

PFA

[annabergendal]

Thanks! That's great...

Now how can I sort the table in the script by Material, so that it looks like in your table. Tried with Order By but it doesn't seem to work... It order by Datum, which means I cannot do the peek thing I would like...

[maxgro]

you can order by when you read from an already in memory table (load ... resident....)

so add

TableC:

noconcatenate load

  *

Resident

TableA

order by ...;

drop table TableA;

[annabergendal]

Thanks a lot!