Excel import and blank cells filling from previous row

One Star

Excel import and blank cells filling from previous row

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.
Community Manager

Re: Excel import and blank cells filling from previous row

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Excel import and blank cells filling from previous row

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!
Seven Stars

Re: Excel import and blank cells filling from previous row

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;
One Star

Re: Excel import and blank cells filling from previous row

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!
Seven Stars

Re: Excel import and blank cells filling from previous row

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.
One Star

Re: Excel import and blank cells filling from previous row

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