Alerts - Email to different addresses

[dhborchardt]

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.

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

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?

[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]

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

[Anonymous]

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]

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.

[dhborchardt]

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?

[Anonymous]

Can you share a sample app with sample data?

[dhborchardt]

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]

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]

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.