Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

saivina2920
Contributor

How to pass multiple values in count expression

I want to pass multiple values in my count expression.

LOAD * Inline [
EMPNO, SECTION
EA1,AA
EB1, BB
EC1, AA
ED1,CC
];

Actually my count expression is below.

=Count({<[EMPNO]={'vEMPNO'},[SECTION]={"$(vSECTION)"}>} DISTINCT [EMPNO])

I want to pass a variable "EMPNO" and "SECTION" as mentioned in count expression.

How to call the above two columns (EMPNO,SECTION) as variable...?

1 Solution

Accepted Solutions
Jagsfan82
New Contributor III

Re: How to pass multiple values in count expression

I'm still not 100% sure what you are looking for based on the expressions your giving me. It looks like you are trying to set the EMPNO field equal to all the values in EMPNO. Also not sure what you mean by vEmpNo = 'EMPNO'. That would be a string.

I'm assuming by "Variable section" you mean variable overview window? (CTRL+ALT+V)

If you want all the values in a field you can try to use the following as the variable:

='{'&Concat(DISTINCT EMPNO,'}+{')&'}'

That should expand out to include all distinct values in the field EMPNO and work in your set analysis. But again, I can't envision what you are trying to achieve?

=Count({<[EMPNO]=$(vEMPNO) >} DISTINCT [EMPNO])
will expand to something like
=Count({<[EMPNO]={E101}+{E102}+{E103} >} DISTINCT [EMPNO])

Again anything else that could make it more clear what you are trying to achieve would be helpful

View solution in original post

8 Replies
Partner
Partner

Re: How to pass multiple values in count expression

Hi ,

 

Can you please elaborate what as a output you want. Do u want distinct value for both EMPNO,SECTION ie for EMPNo EA1 and Section AA you want 1 as output.

 

Regards

Kamal

saivina2920
Contributor

Re: How to pass multiple values in count expression

Whatever values will be there in the column, I want to pass all the values. It is dynamic. It is like where clause of column name in (a,b,c,etc)...
Jagsfan82
New Contributor III

Re: How to pass multiple values in count expression

I'm not sure I'm following what you are looking to do, but for starters, I'm assuming you are using the expression in the front end not the load script. Other than that there's a few issues with the syntax (possible it works)

=Count({<[EMPNO]={'vEMPNO'},[SECTION]={"$(vSECTION)"}>} DISTINCT [EMPNO])
to
=Count({<[EMPNO]={"=$(vEMPNO)"},[SECTION]={"=$(vSECTION)"}>} DISTINCT [EMPNO])

But I would imagine if the variables are formatted like vEMPNO=01,02,03 or '01','02','03' you could get rid of the "=" and just use EMPNO={$(vEMPNO)}
saivina2920
Contributor

Re: How to pass multiple values in count expression

Thanks again..

How do we call this variable from another select/load statement..

Because this variable have to call from another SQL statement..?

How to do this..??

saivina2920
Contributor

Re: How to pass multiple values in count expression

Thanks again..

How do we call this variable from another select/load statement..

Because this variable have to call from another SQL statement..?

How to do this..??
Jagsfan82
New Contributor III

Re: How to pass multiple values in count expression

Set analysis cannot be used in the load script.  For what you are trying to do I would think you could design a simple loop to go back through your old table, but I also feel like there is probably a more elegant way to handle this. 

I'm new to Qlikview myself and never actually built a loop and make a lot of mistakes, but I would think you would need to utilize rowno() and peek(). Someone with more experience may be able to hone this in.  But something along the lines of the following (Which does not work)

Set your variables (May need to play with the single quotes to pass this correctly)

Set vSECTION = 'AA'; 
Set vEMPNO = 'EA1,EB1';

Add a rowno() counter to your first table either manually or loading a new table 

EmployeeTemp:

LOAD * Inline [
EMPNO, SECTION, RowNo
EA1,AA,1
EB1, BB,2
EC1, AA,3
ED1,CC,4
];

----OR----


EmployeeTable:
Load
  EMPNO
  SECTION
  RowNo() as RowNo
Resident EmployeeTemp

And use peek to do your count.  Again someone would have to clean this up so it works properly.. haven't done loops yet.

Set vCount = 0;
Let vEmployeeRows = Peek(RowNo,-1,'EmployeeTable');


For i=1 to $(vEmployeeRows)
    IF   Match(Peek(EMPNO,i-1,'EmployeeTable'),$(vEMPNO)) AND Match(Peek(SECTION,i-1,'EmployeeTable'),$(vSECTION)) THEN
    Let vCount = $(vCount)+1; //Is there a vCount+1 ability in Qlik?
Next

Highlighted
saivina2920
Contributor

Re: How to pass multiple values in count expression

i tried the below expression for single variable passing. but, it is not working.
Script :
TABLE1:
LOAD EMPNO,
EMPNAME,
EMPSECTION;
SQL SELECT *
FROM dbo."EmpDetails";

i declared variable in the variable section and its below
vEmpNo = 'EMPNO';

finally i used expression is below
=Count({<[EMPNO]={$(vEMPNO)} >} DISTINCT [EMPNO])

For testing, i hardcoded value. it is working.

=Count({<[EMPNO]={'E101'} >} DISTINCT [EMPNO])  ==> Working

=Count({<[EMPNO]={$(vEMPNO)} >} DISTINCT [EMPNO]) ==> Not Working ==>  (vEmpNo) value not set properly


where and what is problem and can anyone help regarding this..? This is very urgent.

Jagsfan82
New Contributor III

Re: How to pass multiple values in count expression

I'm still not 100% sure what you are looking for based on the expressions your giving me. It looks like you are trying to set the EMPNO field equal to all the values in EMPNO. Also not sure what you mean by vEmpNo = 'EMPNO'. That would be a string.

I'm assuming by "Variable section" you mean variable overview window? (CTRL+ALT+V)

If you want all the values in a field you can try to use the following as the variable:

='{'&Concat(DISTINCT EMPNO,'}+{')&'}'

That should expand out to include all distinct values in the field EMPNO and work in your set analysis. But again, I can't envision what you are trying to achieve?

=Count({<[EMPNO]=$(vEMPNO) >} DISTINCT [EMPNO])
will expand to something like
=Count({<[EMPNO]={E101}+{E102}+{E103} >} DISTINCT [EMPNO])

Again anything else that could make it more clear what you are trying to achieve would be helpful

View solution in original post