topic Re: How to store multiple values in a single Qlik Variable? in Qlik NPrinting

How to store multiple values in a single Qlik Variable?

Garima2020 — Sat, 16 Nov 2024 00:24:15 GMT

I have a requirement of storing multiple email addresses of users in a single variable so that the variable can be used in NPrinting Task to distribute the mail. I have data in this format-

Employee_Detail:
load * inline [

EmpId,User,Email_id
1,Garima,'garima.kul@abc.com,gar.kuls@abc.com'
2,Kunal,kunal.dhawan@abc.com
3,Naresh,naresh.kr@abc.com
4,Durgesh,durgesh.pl@abc.com
5,Subodh,subodh.chn@abc.com
6,Sayali,sayali.ra@abc.com

];

so the variable should be able to store multiple addresses against a single user., eg for EmpId 1 there are 2 email addresses so variable should store both the mail addresses.

Thanking in anticipation.

Re: How to store multiple values in a single Qlik Variable?

fosuzuki — Tue, 30 Jun 2020 07:34:13 GMT

Hi,

In the loadscript, you add something like this:

tmp:

LOAD concat(distinct Email_id, ',') AS All_Email

resident Employee_Detail;

let vEmails = peek('All_Email', 0, 'tmp');

drop table tmp;

In the layout, you can basically use the same expression: concat(distinct Email_id, ',')

Re: How to store multiple values in a single Qlik Variable?

Lech_Miszkiewicz — Thu, 02 Jul 2020 01:05:46 GMT

@fosuzuki is right. My additional comments below to clarify:

What you have to remember is that you either have to concatenate all emails in script - this will not be a dynamic solution though.

On the other hand if you want to have dynamic emails selected based on user filter you need to create a variable as expression starting with "=" sign in your variable definition.

So your variable should be:

LET vEmails = '=concat(distinct Email_id, ',')';

The reasons for equal sign are explained here: https://help.qlik.com/en-US/nprinting/June2020/Content/NPrinting/Troubleshooting/Variables-dont-expand-before-calculating.htm

Re: How to store multiple values in a single Qlik Variable?

Saravanan_Desingh — Thu, 02 Jul 2020 02:46:28 GMT

Check this code:

Employee_Detail: load * inline [ EmpId,User,Email_id 1,Garima,'garima.kul@abc.com,gar.kuls@abc.com' 2,Kunal,kunal.dhawan@abc.com 3,Naresh,naresh.kr@abc.com 4,Durgesh,durgesh.pl@abc.com 5,Subodh,subodh.chn@abc.com 6,Sayali,sayali.ra@abc.com ]; tab1: LOAD Concat('Let vEmail_'&EmpId&'='&Chr(39)&Email_id&Chr(39),';') As FreeText Resident Employee_Detail; Let vFreeText=Peek('FreeText'); $(vFreeText); Drop Table tab1;

Re: How to store multiple values in a single Qlik Variable?

Saravanan_Desingh — Thu, 02 Jul 2020 02:47:46 GMT

For each EmpId, a variable will be created and stores the Email IDs associated.

Re: How to store multiple values in a single Qlik Variable?

Lech_Miszkiewicz — Thu, 02 Jul 2020 03:00:00 GMT

hi @Saravanan_Desingh

I think your solution although it shows how to dynamically create various variables for each row it does not make sense in this context.

Please note that the topic is in regards to NPrinting. Based on the context I can see that this variable will be used for report distribution in email (variable). So if you create a new variable for each EmpId there will be no easy way of referencing this variable in dynamic email (in NPrinting). The typical and one feasible solution is the one where you have an expression as variable definition (look up mine previous comment ) and you simply reference a variable in your dynamic email FROM, CC or BCC.

Re: How to store multiple values in a single Qlik Variable?

Saravanan_Desingh — Thu, 02 Jul 2020 03:11:38 GMT

I agree with you @Lech_Miszkiewicz

Re: How to store multiple values in a single Qlik Variable?

Steven_Haught — Thu, 02 Jul 2020 15:23:58 GMT

I just created a Nprinting test and it is working good using a small twist on the above information from Lech and company. Instead of inline I am building off a excel sample of some employee data with a new field called special. Inside of the qlik app I placed the following in the variables creator:

=Concat(IF(Special='Y', Testemail), '; ')

You could designate this by dept., role, special designation, etc.

In the Nprinting Task, email section, I just placed the Qlik Variable in the To section. This worked wonderfully and would be easy to change on the fly. Will be using this for special purpose reports!

Re: How to store multiple values in a single Qlik Variable?

Lech_Miszkiewicz — Fri, 03 Jul 2020 01:08:38 GMT

Hi @Steven_Haught

When working with NPrinting always think about performance!!

your formula

=Concat(IF(Special='Y', Testemail), '; ')

should be replaced with

=Concat({<Special*={'Y'}>} Testemail), '; ')

On large data amount it may have a major impact on report production performance!

https://community.qlik.com/t5/Qlik-Design-Blog/Performance-of-Conditional-Aggregations/ba-p/1463021

"...The bottom line is that Set Analysis is the method you should use for large data amounts. For smaller data amounts, it doesn’t really matter which method you choose: They are all fast enough..."

Re: How to store multiple values in a single Qlik Variable?

Steven_Haught — Fri, 03 Jul 2020 12:22:08 GMT

@Lech_MiszkiewiczThat is great point that I overlooked and a wonderful solution. This will ultimately scale up to a very large data set and that would have likely caused performance issues! I will update my notes and keep that handy!

Re: How to store multiple values in a single Qlik Variable?

Garima2020 — Mon, 06 Jul 2020 03:32:49 GMT

I totally agree with you. @Lech_Miszkiewicz