Hitachi Vantara Pentaho Community Forums
Results 1 to 18 of 18

Thread: Add variable or parameter to subjetc in a mail step

  1. #1

    Default Add variable or parameter to subjetc in a mail step

    Hi,

    I need to write automatically the date of the last week in a job because I'm sending a file with content of the last week.
    So every monday I send an email with a file and in the subject I want to write "Inform for the week 14-01-2019" (in this case, the email will be sent the 21-01-2019).


    Could you help me?

    If this is not possible, would it be able to write the automatically date in the name of the excel file with a variable or parameter??

    Thanks in advance.
    Last edited by jficheux; 01-23-2019 at 06:14 AM.

  2. #2
    Join Date
    Nov 2009
    Posts
    688

    Default

    Make a transformation that determined last week and update a variable with that value. You can use that variable in the subject of the mail or the name of an Excel file

  3. #3

    Default

    Quote Originally Posted by johanhammink View Post
    Make a transformation that determined last week and update a variable with that value. You can use that variable in the subject of the mail or the name of an Excel file
    I've tried to do it but I'm doing anything wrong because the variable doesn't show the value. How can I attach an image to show you my .ktr???

    I have a "Table Input" with the value of the date I want to show in the subject or in the excel file name.
    In the Table Input I have:

    SELECT date_format(date_sub(curdate(), INTERVAL 7 DAY), '%Y-%m-%d') as datee
    FROM DUAL

    Then I link this step with "Select Values" step and I add datee.

    After this step in the "Excel" step I wirte in the filename case:

    C:\Users\j.lopez.de.alda\Desktop\Eventos Caidas SAE ${datee}.xls

    But the content of the datee parameter is not written at the name file.

    What am I doing wrong???

    Thanks in advance.

  4. #4
    Join Date
    Aug 2016
    Posts
    290

    Default

    You need to know the difference between a field and a variable. A field is a value that is moved between steps in a transformation and does NOT use the ${NAME} syntax. A variable however is set before the transformation is started, and does not change for the duration of the transformation. A variable usually is referenced with the ${NAME} syntax.

    Before doing complicated stuff with file names and emails, I suggest you make some simple "Hello world" kind of demo jobs and transformations, experimenting with variables. Just write the variable values to log first, to see how you can use a variable.
    Last edited by Sparkles; 01-23-2019 at 08:13 AM.

  5. #5

    Default

    Quote Originally Posted by Sparkles View Post
    You need to know the difference between a field and a variable. A field is a value that is moved between steps in a transformation and does NOT use the ${NAME} syntax. A variable however is set before the transformation is started, and does not change for the duration of the transformation. A variable usually is referenced with the ${NAME} syntax.

    Before doing complicated stuff with file names and emails, I suggest you make some simple "Hello world" kind of demo jobs and transformations, experimenting with variables. Just write the variable values to log first, to see how you can use a variable.
    Hi,
    Thanks for you reply. I know the diffrence between variable and field, but I want to use a variable with a value non specific because it's going to change depends on the day I run the job. Maybe, what I want to do is not possible with spoon.
    The thing I want is to send an email (I already do it, and it's working) but in the subject I need the date of the week before. If is not in the subject on the mail, it would be ok in the name of the excel file. Is that possible???

  6. #6
    Join Date
    Aug 2016
    Posts
    290

    Default

    You can certainly create a variable with the value equal to relative date, no problem! I suggest creating this variable in the parent job before starting your main transformation where this variable is used.

    What step do you use for email exactly? I had a look at the "Send mail" step, and it certainly allows for using fields as Subject! So you could easily get the variable you already created, add it to the stream and use it in this email step.

    You can also change any file names as you like.

  7. #7

    Default

    Quote Originally Posted by Sparkles View Post
    You can certainly create a variable with the value equal to relative date, no problem! I suggest creating this variable in the parent job before starting your main transformation where this variable is used.

    What step do you use for email exactly? I had a look at the "Send mail" step, and it certainly allows for using fields as Subject! So you could easily get the variable you already created, add it to the stream and use it in this email step.

    You can also change any file names as you like.
    Hi,

    Is there any way to attach an image but no as a url????

  8. #8
    Join Date
    Aug 2016
    Posts
    290

    Default

    Sorry I have no experience with email or attaching images. I can only help with variables and changing file names.

  9. #9

    Default

    Quote Originally Posted by Sparkles View Post
    Sorry I have no experience with email or attaching images. I can only help with variables and changing file names.
    Sorry, I meant here, in the forum. To show you what I have done. So it's easier to correct my job.

  10. #10
    Join Date
    May 2016
    Posts
    282

    Default

    Since they "encourage" us to move to the forums in Hitachi Vantara, it's not possible to attach anything to the forum, you can upload things to a external source such as google drive or whatever, and put the url here.
    Anyway, there are two mail steps, one for jobs and one for transformations, that is what I think is confusing @jficheux and you, @Sparkles, you are talking of different steps with slighty different properties and ways of working with them.
    If you want to work with the job step, you need variables as there are no columns in jobs. You might try sending the mail from a transformation if it's easier for you, @jficheux, at least for testing purposes to try to see what you are sending to the mail step.
    Another thing coming into my mind, if you are working with Pentaho 8.1, there was a nasty bug working with variables, I think it affected passing variables from a job to a transformation or subjob, but not inside the same job, but I don't know for sure since I stayed away from that version once I learned about the bug, I work with variables a lot to consider that version as workable.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  11. #11
    Join Date
    Aug 2016
    Posts
    290

    Default

    Yes, good clarifications.

    I must insist that before trying to use variables in email step/job (either in job or transformation), first do a simple demo where the variable is created and printed to log. Once you know the variable is working, then apply email.

  12. #12

    Default

    First of all I have to say that I have no idea how to use variables in spoon, I've never had to do it, so I try this:


    First I tried to edit the name of the file with a variable.

    https://drive.google.com/open?id=12A...zwVQOCTTWcF6B2

    https://drive.google.com/open?id=1QN...I_mYJPr2ZrpsYp


    This image is the other alternative I've tried, instead of change the name of the file, personalize the subject.

    https://drive.google.com/open?id=1qw...5D68r9z9oouz9f

    How can I do it well???

    Thanks in advance.

  13. #13
    Join Date
    Nov 2009
    Posts
    688

    Default

    You cannot use in one transformation a Set Variabeles and use that variable in the same transformation.
    So in the first transformation set the variable.
    In the second transformation use that variable to create a Excel file with the variable in the name. Then you can mail that Excel file

  14. #14

  15. #15
    Join Date
    Aug 2016
    Posts
    290

    Default

    Before doing anything with emails, you have to understand how variables are working!


    In a job, create 2 transformations:

    Transformation nr. 1: Create the variable with the value you want. Use the "Set Variables" step.
    Transformation nr. 2: Use the variable that was created previously. Use the "Get Variables" step, then print to log!!!

  16. #16
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by jficheux View Post
    That looks like it should work, but you haven't mentioned your version... Someone else noted that there were issues with variables in 8.1
    If you post the actual transformations and job on Drive, other forum members can look through it and help you understand where the issue is. Just remember to remove any connection information first!

  17. #17
    Join Date
    May 2016
    Posts
    282

    Default

    It should work, and going by the icons you are working with a old version on Pentaho, so the variables bug of 8.1 shouldn't affect you. Try adding different steps printing the variable, for example, after the variable transformation try adding the Write to file job step, and just create a text dummy file printing the variable ${FECHA} (I've just tried it in Pentaho 6.1 and it works) to see if you are setting it correctly. If the variable doesn't get printed, maybe there's some problem setting the scope of the variable. Read this blog post to see if it gives some light on your problem:
    http://diethardsteiner.blogspot.com/...variables.html
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  18. #18

    Default

    Quote Originally Posted by Sparkles View Post
    Yes, good clarifications.

    I must insist that before trying to use variables in email step/job (either in job or transformation), first do a simple demo where the variable is created and printed to log. Once you know the variable is working, then apply email.
    I've got it!!!!

    Thank you very much for all your help!!!


    Regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.