Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dhborchardt
Partner - Creator
Partner - Creator

Alerts - Email to different addresses

I need to send an email to managers when they have contracts due. I figured out how to trigger an email if the Sum of a column > than a given number but can't figure out how to check each record on the date field. I would like to set it up so upon each reload, if the 'Renew Contract Date' field equals that day, it will trigger an email to that person. On 1/1/2018 one email would go out to Joe. On 2/2/2018 one email would go to Sally and one to Bob and on 4/4 and email would go to Joe, Doug and diane. See table below.

    

NameContractRenewContractDate  Email
joe462341/1/2018  Joe@8r2.com
Sally123422/2/2018  Sally@8r2.com
Bob298462/2/2018  Bob@8r2.com
Tim147893/3/2018  Tim@8r2.com
joe569404/4/2018  joe@8r2.com
Doug906214/4/2018  Doug@8r2.com
Diane746104/4/2018  Diane@8r2.com

Is this possible? I have QlikView desktop 12, QlikView server 11.

Would this be done with an expression in the 'Mail Recipients' field on 'Tools/Alerts'?

Does QlikView check each record as it loads to see if an Alert should be sent?

9 Replies
Marcos_Ferreira_dos_Santos

Hi Dale,

Very interesting issue. As far as my Qlik knowledge reaches, I think it could be done, because we can build an expression while defining mail options at the Alerts screen.

So, I would suggest you to try this in the Mail Subject field:                      Concat({<RenewContractDate_field=Today()>}Email_field,',')

Keep in mind that Qlikview can check any data of your document before sending the alert message.

I hope this could help.

Marcos

bramkn
Partner - Specialist
Partner - Specialist

To add, The seperator might need to be ';'.

Anonymous
Not applicable

Hi Dale,

If you have User ID for the corresponding Name you can try the below snippet.

Load [User ID], Name from XXXXXXX;

LOAD * ,
Today()-RenewContractDate AS Noofdays;
Load * Inline [
Name, Contract, RenewContractDate , Email
joe, 46234, 1/1/2018, Joe@8r2.com
Sally, 12342, 2/2/2018, Sally@8r2.com
Bob, 29846, 2/2/2018, Bob@8r2.com
Tim,14789, 3/3/2018, Tim@8r2.com
joe, 56940, 4/4/2018, joe@8r2.com
Doug ,90621, 4/4/2018, Doug@8r2.com
Diane, 74610, 4/4/2018, Diane@8r2.com
]
;

Mail recipients:

=if([User ID]=QVUser() and Noofdays=0 ,Concat(Email,';'))

Thanks !!

dhborchardt
Partner - Creator
Partner - Creator
Author

Thank you for the example. I could not get it to work. I started slowly simplifying first by giving it one email recipient instead of having to derive by expression. I then tried to simplify the Condition. I cannot get it to work line by line. I am only able to get it to work with a calculation of all records. When I summed the Contract number I was able to get it to send and email.

It does not seem to be looking at each record and acting one by one.

Alerts.png

dhborchardt
Partner - Creator
Partner - Creator
Author

I found that it works if I only have one record in the table. I can also call the email address from that same record.

this is what I need to do for each record. Do I need to do some sort of For Each loop and where would this be done?

1.png

Anonymous
Not applicable

Can you share a sample app with sample data?

dhborchardt
Partner - Creator
Partner - Creator
Author

I simplified the expressions until I got it to work . See attached.   If I just have one record then it will work if the day is correct. As soon as I add other records it fails. How do I make it iterate through the records?

Anonymous
Not applicable

Dale, Please use

=if([User ID]=QVUser() and Noofdays=0 ,Concat(Email,';'))

in mail receipients instead of [Email] alone. (make sure ([User ID]=QVUser() )

dhborchardt
Partner - Creator
Partner - Creator
Author

Thank you for your patience

Not able to get this to work. It tries to send it but I get this error- 

     "Could not connect to Server.

     Last Response: Unexpected RCPT TO response, Last Response:

     501 5.1.3 Invalid address"

I know the email is correct because if I use simply =[Email]       It will work.

I have a some questions on your expression:     =if([UserID]=QVUser() and Noofdays=0 ,Concat(Email,';'))

1. In your example you used [User ID] with a space. Was this a typo or did you mean it to have a space?

2. QVUser -  I don't think QVUser is involved unless section access is being used. I am not using section access.

3. I don't understand the need to compare UserID to QVUser?

4. Concat - This is the first time I have used 'Concat' so I am confused on how it is being used here. Is it meant to create a semicolon delimited list of email addresses so one email goes out to multiple people?

5. Can QlikView Alerts send multiple distinct/separate emails out? For example If Bill's contract is due it will send him an email with the contract number and other details specific to him. Then when QlikView looks at the second record it sees that Joe's contract is also due and sends him his own distinct email?

It seems that everything breaks as soon as I add a second record to the inline load as it does not know what to do with more than one record.