Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...?
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
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..??
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
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.