Excel import and blank cells filling from previous row

[Anonymous]

Hi
I'm comparing Kettle to Talend and even if the learning curve is much steeper it seems to me it provides you a lot of flexibility. I'm trying to achieve the same (simple) result I got with kettle: I'm importing a simple excel, creating the dimensions/facts tables and having some operations on them.
I'm stuck on a pretty simple matter, which kettle solved me with a trivial trick:
the excel I'm processing is aggregated for shop and company (something like "shop, company, sale" and shop is given just for the first row). Kettle solved this with a tick on the excel component on the "shop" column so that it automatically repeats the previous value in case the current cell is blank.
I think i'm dumb as i can't find the solution in the forum nor by google.

[Anonymous]

Hi
In talend, there is a tAggregateRow component with which you can do all the aggregate process and the get first value. If you have trouble to use this component, please show us an example and what are your expected result, I can show you a demo job.
Best regards
Shong

[Anonymous]

Hi
In talend, there is a tAggregateRow component with which you can do all the aggregate process and the get first value. If you have trouble to use this component, please show us an example and what are your expected result, I can show you a demo job.
Best regards
Shong

thanks Shong but I'm unable to use the tAggregateRow component in the way I need (i've tried your example):
I have an excel file like this:
SHOP1 VALUE VALUE
VALUE VALUE
VALUE VALUE
SHOP2 VALUE VALUE
VALUE VALUE
and i want to obtain
SHOP1 VALUE VALUE
SHOP1 VALUE VALUE
SHOP1 VALUE VALUE
SHOP2 VALUE VALUE
SHOP2 VALUE VALUE
any hint? any help is truly appreciated!

[alevy]

Between your input component and tAggregateRow have a tJavaRow with the following:

if (input_row.shop==null) {
  output_row.shop = (String)globalMap.get("PreviousShop");
} else {
  output_row.shop=input_row.shop;
  globalMap.put("PreviousShop",input_row.shop);
}
output_row.value1=input_row.value1;
output_row.value2=input_row.value2;

[Anonymous]

Between your input component and tAggregateRow have a tJavaRow with the following:

if (input_row.shop==null) {
  output_row.shop = (String)globalMap.get("PreviousShop");
} else {
  output_row.shop=input_row.shop;
  globalMap.put("PreviousShop",input_row.shop);
}
output_row.value1=input_row.value1;
output_row.value2=input_row.value2;

thanks alevy, I just thought custom code would have represented the last solution and talend could have delivered the same result in a "standardized" way (like Kettle)!
Thanks anyway!

[alevy]

It's an interesting idea. Why don't you submit a feature request on the BugTracker for all file input components to have such a feature and maybe the reverse for file output components.

[Anonymous]

This is interesting point and is there any solution to it?