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: 
sayadutt
Creator
Creator

Urgent - Sending periodic autogenerated emails based on a field value

Hi Team,

I have a Table List which consists of:

Division     Project Name     Release No     Compliance Status

XXX          A                             1.1               Compliant

YYY          B                             2.0              Non Compliant

XXX          A                             1.2               Non Compliant

Now I need to send an auto-generated email each Monday and Thursday to a certain group containing only the Non Compliant items.

So, here from the above example, the email body should be:

Below are the Non Complaint items:

Division     Project Name     Release No

YYY          B                         2.0

XXX          A                         1.2

Can you please guide me how can I send this reminders automatically each Monday and Thursday.

Thanks

6 Replies
pokassov
Specialist
Specialist

Hi, Sayantan.

First of all, you should create a chart (straight table) with your fields.

For expression you can use something like this sum(if([Compliance Status]='Non Compliant', 1))

You can hide this expression in the Presentation Tab selecting your expression and checking Hide Column

Next you should write a macros. You can use examples from community. This is one of:

Send Mail from QlikView with table in the mail body

You can set up a trigger "OnPostReload" and call macros there

You might create a list of emails, which you will be able to use to send message

You can add variable vReport

=Match(num(WeekDay(Today())),1,2)

Just check your regional settings to find out which numbers have Monday and Thursday.

You have to add condiction in your macros to check if your variable equal 1 then you go through the macros

You can use loop to send mail for everybody in your email list.

You can write bat file to launch your document

"c:\Program Files\QlikView\qv.exe" /r  "your path\your document.qvw"

And finally you can set schedule up for launch bat file every day.

Sergey.

petter
Partner - Champion III
Partner - Champion III

I put together a simple demo that works in my environment from techniques I have been using in other projects.

Have a look at the screenshots and the attached QlikView application .QVW.

Using PowerShell and the command Send-MailMessage:

2015-04-12 #1.PNG

2015-04-12 #3.PNG

2015-04-12 #2.PNG

sayadutt
Creator
Creator
Author

Hi Peter,

Thanks for your reply.

I am trying to run your attached example in my system.

I modified line 39.

EXECUTE powershell.exe Send-MailMessage -To <myid@domain.com> -From <myid@domain.com> -Subject \"Test of Mail-Messaging\" -BodyAsHTML \"$(v.MailTxt)\" -SmtpServer smtp.getmail.no;

Do I need to modify SmtpServer smtp.getmail.no;

or this is generic?


When I load with the above commend, reload is successful (it didn't ask for credentials) ., But no email was sent to me.


Can you please suggest.




Thanks

sayadutt
Creator
Creator
Author

Hi Сергей,

I am pretty new to qlikview. Wil it be possible for you to give me a sample qvw file?

Thanks

petter
Partner - Champion III
Partner - Champion III

Yes it might look at bit generic but it is actually not. It has to be replaced with your "closest" SMTP-server. In a business there is usually an SMTP-server you can use which does not need credentials. Then you simply replace smtp.getmail.no with the name of your organisations SMTP-server.

You can even use gmail or some ISP-providers SMTP-server as long as you have rights to use it...

You should test the Send-MailMessage statement directly from the PowerShell commandline to see what the response is with confirmation etc before you try to include it in your load script.

petter
Partner - Champion III
Partner - Champion III

Hi - could you please close the thread by marking it as answered or follow-up the response?

kind regards

Petter Skjolden