Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kiranb13
Contributor II
Contributor II

Passing multiple values in Qlik variable input

Hi Team,

I need help in passing multiple values to the SQL for on demand reporting. Here in the below SQL i need to pass 2 values. From the SQL query output i need to pick NMISPID where it has 6 values i need to pass it as array for the NMI version table.

Ex: 

NMI:
LOAD
distinct NMISPID,
NMI;

SELECT NMISPID, MPCORE AS NMI FROM mbswp.nmis nmi

where mpcore in($(vNMI));

Let aListLength = NoOfRows('NMI');

For zi = 0 To aListLength - 1

Let aList$(zi) = Peek('NMISPID', zi, 'NMI');
LOAD $(aList$(zi)) As NMISPID1
AUTOGENERATE 1;

Next
trace 'NMISPID1' ;

Let vNMISPID=$(aList$(zi));

FOR vRecNo = 1 to NoOfRows('NMI')
let vNMISPID=Peek('NMISPID',vRecNo-1,'NMI');


inner join(NMI)
NMI_Versions:
LOAD
distinct NMISPID,
NMI_DATE_END,
BCFMNM,
BCGVNM,
BUNM,
CFMLNM,
CGVNNM;

SELECT NMISPID, DATEEND as NMI_DATE_END, BCFMNM, BCGVNM, BUNM, CFMLNM, CGVNNM, PADDR FROM mbswp.nmi_versions nmiv
where NMISPIDin($(vNMISPID));

i am getting error with vNMISPID which has multiple values.

Can anyone help me how to handle this?

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this may be

NMI:
LOAD Concat(DISTINT Chr(39) & NMISPID & Chr(39), ',') as ConcatenatedNMISPID;
SELECT NMISPID, MPCORE AS NMI FROM mbswp.nmis nmi;

LET vNMISPID = Peek('ConcatenatedNMISPID');

NMI_Versions:
LOAD DISTINCT NMISPID,
     NMI_DATE_END,
     BCFMNM,
     BCGVNM,
     BUNM,
     CFMLNM,
     CGVNNM;
SELECT NMISPID,
       DATEEND as NMI_DATE_END,
       BCFMNM,
       BCGVNM,
       BUNM,
       CFMLNM,
       CGVNNM,
       PADDR
FROM mbswp.nmi_versions nmiv
WHERE NMISPID in ($(vNMISPID));

View solution in original post

6 Replies
sunny_talwar

I am not sure I understand what you are trying to do. Are you looking to load only those rows from the qvwd where the NMISPID matches with the NMISPID from the SQL?

kiranb13
Contributor II
Contributor II
Author

Hi Sunny,

I am trying to pass values to SQL. I have altered the query.

I need to pass 2 values as input. I might n number of values as the output. Can you please suggest me how to store  this n no of values and pass it to the SQL.

sunny_talwar

But in the above script, it seems you are passing the where clause to QVD? Is that QVD load really be a SQL query? Is that what you are saying?

kiranb13
Contributor II
Contributor II
Author

Hi Sunny,

I need to pass list of values of the output of 1st table as input to the 2nd table.  So that i can filter the values of the 2nd table as the 2nd table size is big.

Can you please suggest me ?

 

sunny_talwar

Try this may be

NMI:
LOAD Concat(DISTINT Chr(39) & NMISPID & Chr(39), ',') as ConcatenatedNMISPID;
SELECT NMISPID, MPCORE AS NMI FROM mbswp.nmis nmi;

LET vNMISPID = Peek('ConcatenatedNMISPID');

NMI_Versions:
LOAD DISTINCT NMISPID,
     NMI_DATE_END,
     BCFMNM,
     BCGVNM,
     BUNM,
     CFMLNM,
     CGVNNM;
SELECT NMISPID,
       DATEEND as NMI_DATE_END,
       BCFMNM,
       BCGVNM,
       BUNM,
       CFMLNM,
       CGVNNM,
       PADDR
FROM mbswp.nmi_versions nmiv
WHERE NMISPID in ($(vNMISPID));

View solution in original post

kiranb13
Contributor II
Contributor II
Author

Thanks Sunny.

Its working fine now.