Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
dhborchardt
Contributor

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_2012
New Contributor III

Re: Alerts - Email to different addresses

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

Partner
Partner

Re: Alerts - Email to different addresses

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

ashokkumarm
New Contributor

Re: Alerts - Email to different addresses

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
Contributor

Re: Alerts - Email to different addresses

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
Contributor

Re: Alerts - Email to different addresses

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

ashokkumarm
New Contributor

Re: Alerts - Email to different addresses

Can you share a sample app with sample data?

dhborchardt
Contributor

Re: Alerts - Email to different addresses

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?

ashokkumarm
New Contributor

Re: Alerts - Email to different addresses

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
Contributor

Re: Alerts - Email to different addresses

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.