Filling Blank rows with previous non-null value from the same column

Yash_Choudhari

Raw Data :

Product Values

A
	23
	24
	25
B
	45
	56
	76
C
	34
D
	55

Expected Output :

Product Values

A
A	23
A	24
A	25
B
B	45
B	56
B	76
C
C	34
D
D	55

Here i am trying to fill blank rows with the previous non-null value in same column and i want to accomplish above scenario in MagicETL, any help will be highly appreciated.
Thanks in advance.

ArborRose

Without seeing a dataset or column values, I have to generalize and create some fake data. Let's say I have two tables represented here by webforms. The one one the left contains headers or something representing the titles or bigger things. The right being the detailed raw data. If I am joining the data, there needs to be a common field. In this case I use Chapter to join up with the Product Group.

Depending upon how I join these, I get something like this.

It's hard to tell where the header is and whether the continuing detail rows are. How can we find the first row in each chapter? One way is to use Rank and Window in the ETL. By adding a rank, I can denote which lines are the header and which are details.

Using that rank (called myrank in my example), I can create a calculated field. Let's call it vHeading. Whenever the rank is 1, I know its a heading. So I want to show my heading. When the rank is something else, I may want blank space under my heading. I call this nesting.

Case WHEN myrank=1 then Headings else '' end

When I apply it to the data, I get something like the following. If I want my headings on every line, I simply put it on the chart. If I want them only to appear in the first entry line, I can use the calculated field.

Note that I can create a calculated field using rank, without putting rank on the card. You'll notice also that my first heading has a space to the right. Look back at the details data and you'll see I have an entry with no index value. I may want that, or I may want to filter blanks and nulls out.

Hopefully this gives you enough information to solve the issue.

ArborRose

Without seeing a dataset or column values, I have to generalize and create some fake data. Let's say I have two tables represented here by webforms. The one one the left contains headers or something representing the titles or bigger things. The right being the detailed raw data. If I am joining the data, there needs to be a common field. In this case I use Chapter to join up with the Product Group.

Depending upon how I join these, I get something like this.

It's hard to tell where the header is and whether the continuing detail rows are. How can we find the first row in each chapter? One way is to use Rank and Window in the ETL. By adding a rank, I can denote which lines are the header and which are details.

Using that rank (called myrank in my example), I can create a calculated field. Let's call it vHeading. Whenever the rank is 1, I know its a heading. So I want to show my heading. When the rank is something else, I may want blank space under my heading. I call this nesting.

Case WHEN myrank=1 then Headings else '' end

When I apply it to the data, I get something like the following. If I want my headings on every line, I simply put it on the chart. If I want them only to appear in the first entry line, I can use the calculated field.

Note that I can create a calculated field using rank, without putting rank on the card. You'll notice also that my first heading has a space to the right. Look back at the details data and you'll see I have an entry with no index value. I may want that, or I may want to filter blanks and nulls out.

Hopefully this gives you enough information to solve the issue.