One Star

JasperETL multiple output tables

Hi!!
I have 2 input tables with the following columns:
1/ Downloads: customer email, product name, download date
2/ Purchase: customer email, product name, purchase date
After integration, I would like to see 2 tables:
1/ Days_to_purchase: customer email, product name, number of days to purchase (days_to_purchase)
2/ Count_days: product name, days_to_purchase <1, days_to_purchase<30
Is it possible to create table 2 with JasperETL? Can I use SQL queries to create this table? If yes, what function in JasperETL allows me to do this?
Thanks
3 REPLIES
Community Manager

Re: JasperETL multiple output tables

Hello
You can use txxxRow component to execute a create sql to the table at the begining of job.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: JasperETL multiple output tables

Hi Shong!
Can you be more specific? the Count_days table should be generated based on Days_to_purchase table because it will count the number of customers who purchase product within 1 day or 1 month. Days_to_purchase will be generated during integration but Count_days will be generated after we have Days_to_purchase. Is it possible to do that with JasperETL?
Thanks
Community Manager

Re: JasperETL multiple output tables

Hello
In fact, you can create the table if it does not exist on tMysqOutput before inserting records into table. For example:
do your integration---tMysqlOutput_1
|
onSubJobOk
|
tJava
|
RunIf
|
tFixedFlowInput or tMysqlInput(select product_name, days_to_purchase from Days_to_purchase) ---tMysqlOutput_2
on tMysqlOutput_1: create the table Days_to_purchase if it does not exists before inserting records into table
on tJava: use the global varSmiley Sad(Integer)globalMap.get("tMysqlOutput_1_NB_LINE_INSERTED")) to check if you have new inserted rows, for exampe:
if((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_INSERTED") >1){
globalMap.put("weHaveDaysToPurchase",true);
}else{
globalMap.put("weHaveDaysToPurchase",false);
}

on runIf expression, set the condition as:
(Boolean)globalMap.get("weHaveDaysToPurchase")

On tMysqlOutput_2, create the table if it does not exist before inserting records into table for the first time, it based on your need.
Here I just gave you a hint, hope you understand what I mean!!!
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business