topic How to Pass colum in for loop in QlikView

How to Pass colum in for loop

debabrata_sahoo — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have list of shipment id in a qvd. How I will use these id in for loop to run another sql query sql Script.

Example;

Shipment id

91064
94353
98477
98478
98479
98480
98481
98482
98483
98528
98564
98565

SQL select col1,col2 from table1 where Shipemetid = ? (Output should based on all shipment id in qvd)

Here I can't use IN operator . Please help on above requirements.

Thanks,

Deb

Re: How to Pass colum in for loop

marcus_sommer — Fri, 22 Mar 2019 15:31:03 GMT

If there are not too many ID's you could use IN(), for example with:

ShipmentID: load concat(ID, ',') as ID from ShipmentID;
let vShipmentID = peek('ShipmentID', 0, 'ShipmentID');

SQL select * from Source where ShipmentID IN($(vShipmentID));

- Marcus

Re: How to Pass colum in for loop

debabrata_sahoo — Mon, 25 Mar 2019 04:58:27 GMT

Thanks Marcus,

But In my scenario I can't use IN() as per client requirement.

Re: How to Pass colum in for loop

Vegar — Mon, 25 Mar 2019 06:38:09 GMT

The script below will query the database multiple times, but it will do the trick without using IN().

For each vShipmentID in FieldList('shipmentListField')
//autoconcatenate
Shipments:
LOAD*;
SQL select * from Source where ShipmentID = $(vShipmentID);
NEXT vShipmentID

Re: How to Pass colum in for loop

marcus_sommer — Mon, 25 Mar 2019 07:40:56 GMT

What speaks against an in() where-clause? If it's not a problem to pull the whole table you could do that and filter then the data within Qlik per exists(). Otherwise it would be helpful you give a bit more background to your question.

- Marcus