topic Re: How to pass multiple values in count expression in QlikView

How to pass multiple values in count expression

saivina2920 — Sat, 16 Nov 2024 03:35:36 GMT

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

Re: How to pass multiple values in count expression

kamalqlik — Tue, 14 May 2019 09:24:06 GMT

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

Re: How to pass multiple values in count expression

saivina2920 — Tue, 14 May 2019 09:55:52 GMT

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

Re: How to pass multiple values in count expression

Jagsfan82 — Tue, 14 May 2019 14:10:24 GMT

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)}

Re: How to pass multiple values in count expression

saivina2920 — Wed, 15 May 2019 07:46:24 GMT

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

Re: How to pass multiple values in count expression

saivina2920 — Wed, 15 May 2019 09:22:23 GMT

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

Re: How to pass multiple values in count expression

Jagsfan82 — Wed, 15 May 2019 13:02:06 GMT

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

Re: How to pass multiple values in count expression

saivina2920 — Thu, 16 May 2019 13:33:36 GMT

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.

Re: How to pass multiple values in count expression

Jagsfan82 — Thu, 16 May 2019 22:06:16 GMT

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