Question 207171 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Pentaho Input Text Set Field Null to Data Before

It is hard to explain this in word, so sorry if the title is mismatch.

So I have a file text like this:

Header A     Header B     Header C    Header D
Apple        Red          A           100
                          B           200
                          B           300
             Green        A           400
                          B           500
Eggplant     Purple       A           600
                          B           700
             White        a           800

And I want it to be read like this:

Header A     Header B     Header C    Header D
Apple        Red          A           100
Apple        Red          B           200
Apple        Red          B           300
Apple        Green        A           400
Apple        Green        B           500
Eggplant     Purple       A           600
Eggplant     Purple       B           700
Eggplant     White        a           800

But I confuse what transformation that I have to use.
So how to get this result in Pentaho? Or is there video tutorial I have to watch?
Thanks Before.

I have try transformation "If Value is null" and "Filter Rows", but it’s doesn’t seems the solution.

Tags:

2

Answers


  1. 0

    Please try one of this solutions.
    I don t use Postgresql but i m confident that this solutions will work also in Postgresql with small changes for exemple top 1 with limit 1 at the end.

    If you have further questions or is something which you don t understand please ask me.
    Do not use that update after the first solution if not the case.

    with flo as
(select headera, headerb ,row_number ()over(order by (select null))as rn
 from prob1 ), 
flo1 as (
select *, last_value(headera)ignore nulls over(order by rn)as last,
last_value(headerb)ignore nulls over(order by rn)as last1
from flo)
update flo1
set headera= last , headerb=last1
from flo1
where headera is null or headerb is null
select * from prob1
drop table prob1

with flo as
 (select *, row_number ()over(order by (select null))as rn from prob2)
select headera, 
case when headera is null then(select top 1 headera from flo b 
where b.rn<a.rn and b.headera is not null order by b.rn desc) else headera end as headera1,
headerb,
case when headerb is null then(select top 1 headerb from flo c 
where c.rn<a.rn and c.headerb is not null  order by c.rn desc) else headerb end as headerb1
from flo a

with flo as
 (select *, row_number ()over(order by (select null))as rn from prob2),
flo1 as
 (select headera, count(headera)over(order by rn)as cate ,
headerb, count(headerb)over(order by rn)as cate1
from flo)
select headera, first_value(headera)over(partition by cate order by cate)as headeracor,
 headerb, first_value(headerb)over(partition by cate1 order by cate1)as headerbcor
from flo1
    Login or Signup to reply.
  2. 0

    You can use the Modified JavaScript Value step.

    You create two new columns, AltHeaderA and AltHeaderB (or you could rewrite HeaderA and HeaderB columns, but I like keeping the original values for clarity when debugging.

    In the script, you just put:

    if (HeaderA != null) {
    AltHeaderA = HeaderA;
}
if (HeaderB != null) {
    AltHeaderB = HeaderB;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search