Passing multiple values in Qlik variable input

[kiranb13]

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?

[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]

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]

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

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

[kiranb13]

Thanks Sunny.

Its working fine now.