topic Re: how to pass parameter dynamically without manual entry in Talend Studio

how to pass parameter dynamically without manual entry

Darmesh — Fri, 25 May 2018 08:38:45 GMT

Hi,

requirement is for every quarter i will go and change the parameter like mar-18 or sep-18 etc.

Instead of that i have to load it dynamically without entering every time for each quarter.

I have attached test.xlsx which is the parameter file which contains company name, and 8 column with name parm 1,2,... so i am passing each parameter value in sql query.

SQL Query

INSERT INTO temp_report(SELECT
distinct b.bbcode,b.COMPANY ,b.Reco,b.Price,
(SELECT round(NET_SALES) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Revenue_Rs_m,
(SELECT round(NET_SALES) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM2"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Year,
(SELECT round(NET_SALES) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM3"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Quarter,
(SELECT CAST(((Revenue_Rs_m-R_Year)/R_Year)*100 as decimal(10,1)) 'Revenue_Rs_m YOY%' FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m YOY%',
(SELECT CAST(((Revenue_Rs_m-R_Quarter)/R_Quarter)*100 as decimal(10,1)) 'Revenue_Rs_m QOQ%' FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m QOQ%',
(SELECT CAST(EBITDA_MARGIN as decimal(10,1)) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'EBITDA_MARGIN_Dec-17',
(SELECT CAST(EBITDA_MARGIN as decimal(10,1)) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM2"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'EBITDA_MARGIN_Dec-16',
(SELECT round(ADJ_NET_PROF) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Adjusted_PAT,
(SELECT round(ADJ_NET_PROF) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM2"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Year,
(SELECT round(ADJ_NET_PROF) FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM3"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Quarter,
(SELECT CAST(((Adjusted_PAT-A_Year)/A_Year)*100 as decimal(10,1)) 'Adjusted_PAT YOY%' FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT YOY%',
(SELECT CAST(((Adjusted_PAT-A_Quarter)/A_Quarter)*100 as decimal(10,1)) 'Adjusted_PAT QOQ%' FROM qtr_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM1"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT QOQ%',
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Revenue_Rs_m1,
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM4"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Year1,
(SELECT CAST(((Revenue_Rs_m1-R_Year1)/R_Year1)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m YOY1%',
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Adjusted_PAT_Rs_m1,
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM4"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Year1,
(SELECT CAST(((Adjusted_PAT_Rs_m1-A_Year1)/A_Year1)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT_Rs_m_YOY1%',
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Revenue_Rs_m2,
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Year2,
(SELECT CAST(((Revenue_Rs_m2-R_Year2)/R_Year2)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m YOY2%',
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Adjusted_PAT_Rs_m2,
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Year2,
(SELECT CAST(((Adjusted_PAT_Rs_m2-A_Year2)/A_Year2)*100 as decimal(10,1)) 'Adjusted_PAT_Rs_m_YOY%' FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT_Rs_m_YOY%2',
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Revenue_Rs_m3,
(SELECT round(NET_SALES) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS R_Year3,
(SELECT CAST(((Revenue_Rs_m3-R_Year3)/R_Year3)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Revenue_Rs_m YOY%3',
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS Adjusted_PAT_Rs_m3,
(SELECT round(ADJ_NET_PROF) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS A_Year3,
(SELECT CAST(((Adjusted_PAT_Rs_m3-A_Year3)/A_Year3)*100 as decimal(10,1)) FROM sch6_template WHERE YYYYMM = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS 'Adjusted_PAT_Rs_m_YOY%3',
(SELECT CAST((RT_ADJ_EPS_RS) as decimal(10,1)) FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY_1,
(SELECT CAST((RT_ADJ_EPS_RS) as decimal(10,1)) FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY_2,
(SELECT CAST((RT_ADJ_EPS_RS) as decimal(10,1)) FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY_3,
(SELECT CAST((price/FY_1) as decimal(10,1)) FY18_X FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM5"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY18_X,
(SELECT CAST((price/FY_2) as decimal(10,1)) FY19_X FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM6"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY19_X,
(SELECT CAST((price/FY_3) as decimal(10,1)) FY20_X FROM sch6_template WHERE yyyymm = '" + ((String)globalMap.get("test_log.PARM7"))+"' and BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"') AS FY20_X
FROM price_and_reco b where b.BBCODE='" + ((String)globalMap.get("test_log.BBCODE"))+"');

Logic is if my current year is dec-17, then for quarter i need sep-17 and for year i need dec-16

the above is first requirement

similarly for next quarter is mar-18 then, i should not go to the parameter file and change the values.

so i need for quarter dec-17 and for year mar-17 automatically

Re: how to pass parameter dynamically without manual entry

TRF — Sat, 26 May 2018 10:34:14 GMT

Your question is not so clear but you should have a look at Talend Date routines chapter from documentation.

However, from my understanding of what you want, this piece of code may help you:

Date lastDec = TalendDate.ADD_TO_DATE(TalendDate.setDate(TalendDate.getCurrentDate(), 12, "MM"), "YYYY", -1);
System.out.println("lastDec: "+TalendDate.TO_CHAR(lastDec, "yyyyMM"));

Date lastQ3 = TalendDate.ADD_TO_DATE(TalendDate.setDate(lastDec, 12, "MM"), "MONTH", -3);
System.out.println("lastQ3: "+TalendDate.TO_CHAR(lastQ3, "yyyyMM"));

Date prevYear = TalendDate.ADD_TO_DATE(lastDec, "YYYY", -1);
System.out.println("prevYear: "+TalendDate.TO_CHAR(prevYear, "yyyyMM"));

Date nextQ1 = TalendDate.ADD_TO_DATE(lastDec, "MONTH", +3);
System.out.println("nextQ1: "+TalendDate.TO_CHAR(nextQ1, "yyyyMM"));

Just copy/past to a tJava component and run the job.

Here is the result:

Démarrage du job test a 11:33 26/05/2018.
[statistics] connecting to socket on port 3393
[statistics] connected
lastDec: 201712
lastQ3: 201709
prevYear: 201612
nextQ1: 201803
[statistics] disconnected
Job test terminé à 11:33 26/05/2018. [Code sortie=0]

Re: how to pass parameter dynamically without manual entry

Darmesh — Mon, 28 May 2018 12:48:55 GMT

Hi,

can you tell me how to pass the output values in sql query.

Explain me in steps.

Re: how to pass parameter dynamically without manual entry

TRF — Mon, 28 May 2018 13:03:06 GMT

You just have to replace Java locale variable from the example by global variable that you can reuse into your SQL statement as it is in your 1rst query.

Use a tJavaRow to place the necessary code for each needed variable.

For example:

globalMap.put("lastDec", 
    TalendDate.TO_CHAR(TalendDate.ADD_TO_DATE(
        TalendDate.setDate(TalendDate.getCurrentDate(), 12, "MM"), "YYYY", -1), "yyyyMM"));

globalMap.put("lastQ3", 
    TalendDate.TO_CHAR(TalendDate.ADD_TO_DATE(
        TalendDate.setDate(lastDec, 12, "MM"), "MONTH", -3), "yyyyMM");

You may also put the code directly into your SQL statement but as it is long, it seems not a good idea.

Re: how to pass parameter dynamically without manual entry

Darmesh — Wed, 30 May 2018 06:35:31 GMT

Hi TRF,

Can you explain me steps with components. I am new to talend.

Re: how to pass parameter dynamically without manual entry

Darmesh — Mon, 04 Jun 2018 07:46:17 GMT

Hi TRF,

still am not able solve this.

whether the component which i have used is correct. if not let me know the steps.

Re: how to pass parameter dynamically without manual entry

TRF — Mon, 04 Jun 2018 08:23:53 GMT

2 things you have to change:

tJavaRow should not br the 1rst component of your subjob as it expect an input flow. If you don't have an input flow, just replace tJavaRow by tJava
the variables you set in your component are "lastDec" and "lastQ3" but in your tMySqlRow you use "test_log.lastDec" and "last_log.lastQ3" and I'm afraid they null, so you have to change the variables name

Re: how to pass parameter dynamically without manual entry

Darmesh — Tue, 05 Jun 2018 07:07:52 GMT

Thanks TRF,

that's works fine.

one doubt. when i'm passing the below code in tjava, i am getting the output.

Date lastDec = TalendDate.getCurrentDate();
System.out.println("lastDec: "+TalendDate.TO_CHAR(lastDec, "MMM-yy"));

O/P

Jun-18

the same code am passing thru globalvar, am getting null in tjava.

correct me on the code.

Re: how to pass parameter dynamically without manual entry

TRF — Tue, 05 Jun 2018 07:46:20 GMT

I think there is a space char before "lastDec" in the globalMap.put opteration.

Can you check it?

Re: how to pass parameter dynamically without manual entry

Darmesh — Tue, 05 Jun 2018 07:51:26 GMT

Checked...there is no space .

Re: how to pass parameter dynamically without manual entry

TRF — Tue, 05 Jun 2018 08:11:49 GMT

Works fine for me.

Retry with this piece of code:

globalMap.put("lastDec", TalendDate.TO_CHAR(TalendDate.getCurrentDate(), "YYYYMM"));
System.out.println("global: " + globalMap.get("lastDec"));

What's the value printed on the console?

Re: how to pass parameter dynamically without manual entry

Darmesh — Tue, 05 Jun 2018 09:06:41 GMT

still am not getting any values.

these are all the code, which i want to change it to globalmap.put

Date lastDec =TalendDate.parseDate("yyyy-MM-dd", TalendDate.getDate("yyyy-MM-dd"));
System.out.println("param1: "+TalendDate.TO_CHAR(lastDec, "MMM-yy"));

Date prevYear1 = TalendDate.ADD_TO_DATE(lastDec, "YYYY", -1);
System.out.println("param3: "+TalendDate.TO_CHAR(prevYear1, "MMM-yy"));

Date prevYear = TalendDate.ADD_TO_DATE(lastDec, "MM", -3);
System.out.println("param2: "+TalendDate.TO_CHAR(prevYear, "MMM-yy"));

Date Year2 = TalendDate.ADD_TO_DATE(TalendDate.setDate(TalendDate.getCurrentDate(), 03, "MM"), "YYYY", 0);
System.out.println("para5: "+TalendDate.TO_CHAR(Year2, "MMM-yy"));

Date Year1 = TalendDate.ADD_TO_DATE(Year2, "YYYY", -1);
System.out.println("param4: "+TalendDate.TO_CHAR(Year1, "MMM-yy"));

Date Year3 = TalendDate.ADD_TO_DATE(Year2, "YYYY", +1);

System.out.println("param6: "+TalendDate.TO_CHAR(Year3, "MMM-yy"));

Date Year4 = TalendDate.ADD_TO_DATE(Year3, "YYYY", +1);
System.out.println("param7: "+TalendDate.TO_CHAR(Year4, "MMM-yy"));

kindly help me out.

Re: how to pass parameter dynamically without manual entry

Darmesh — Tue, 05 Jun 2018 11:26:48 GMT

Hi TRF,

I have tried the code using globalmap

1. globalMap.put("lastDec",
TalendDate.TO_CHAR(TalendDate.ADD_TO_DATE(
TalendDate.setDate(TalendDate.getCurrentDate(), 12, "MM"), "YYYY", -1), "yyyyMM"));
2. globalMap.put("lastQ3",
TalendDate.TO_CHAR(TalendDate.ADD_TO_DATE(
TalendDate.setDate(lastDec, 12, "MM"), "MONTH", -3), "yyyyMM"));

only first one is working. 2nd is not working. similarly i need for the remaining code