How to pass multiple values in count expression

[saivina2920]

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...?

[kamalqlik]

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]

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]

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]

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]

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]

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

[saivina2920]

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 · Accepted solution]

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