Six Stars

how to pass parameter dynamically without manual entry

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

1 ACCEPTED SOLUTION

Accepted Solutions
Forteen Stars TRF
Forteen Stars

Re: how to pass parameter dynamically without manual entry

2 things you have to change:

  1. 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
  2. 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

TRF
12 REPLIES
Forteen Stars TRF
Forteen Stars

Re: how to pass parameter dynamically without manual entry

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]

TRF
Six Stars

Re: how to pass parameter dynamically without manual entry

Hi,

 

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

 

Explain me in steps. 

 

 

Forteen Stars TRF
Forteen Stars

Re: how to pass parameter dynamically without manual entry

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.


TRF
Six Stars

Re: how to pass parameter dynamically without manual entry

Hi TRF,

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

Six Stars

Re: how to pass parameter dynamically without manual entry

Hi TRF,

 

still am not able solve this.

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

 

a.PNGb.PNG

 

Forteen Stars TRF
Forteen Stars

Re: how to pass parameter dynamically without manual entry

2 things you have to change:

  1. 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
  2. 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

TRF
Six Stars

Re: how to pass parameter dynamically without manual entry

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.

 

a.PNGb.PNG

Forteen Stars TRF
Forteen Stars

Re: how to pass parameter dynamically without manual entry

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

Can you check it?


TRF
Six Stars

Re: how to pass parameter dynamically without manual entry

Checked...there is no space .

Tags (1)
Forteen Stars TRF
Forteen Stars

Re: how to pass parameter dynamically without manual entry

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?


TRF
Six Stars

Re: how to pass parameter dynamically without manual entry

still am not getting any values.

 

a.PNGb.PNG

 

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.

Six Stars

Re: how to pass parameter dynamically without manual entry

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