Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jmclaren
New Contributor II

How to pass values from one loaded table as sql parameters to another

Hi. I'm trying to read data from two tables that share a common field, 'PartNo'. Both tables are massive and the only records I want to load are ones created today (usually not more than 100 records). The first table has a 'Date_created' field, so that's easy, but how do I load only the fields from the second table that match the first? If I try and load all the component records it takes too long. The script i've tried below gives the error:

"[Informix] A subquery has returned not exactly one row.
Components:
load Parent as PartNo, Component, Usage"

I just don't think my sql select on the second table is the right way to do it...


Note: The component_data table might have no matching parts or several.

 

---- Script ----
LET vToday = Date(Today(),'DD/MM/YYYY');


Parts:
load PartNo, Description, Date_created;
sql select * from part_data where Date_created = '$(vToday)';

Components:
load Parent as PartNo, Component, Usage;
sql select * from component_data
where Parent = (select distinct PartNo from part_data where Date_created = '$(vToday)')

 

The 'Parts' load returns 33 records, and I would expect the 'Components' load (when working) would return about 50-60 records.

Labels (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: How to pass values from one loaded table as sql parameters to another

You could try the following approach:

Parts:
load PartNo, Description, Date_created;
sql select * from part_data where Date_created = '$(vToday)';

FilterList: load distinct concat(PartNo, ',') as FilterList resident Parts:

let vFilterList = peek('FilterList', 0, 'FilterList');

Components:
load Parent as PartNo, Component, Usage;
sql select * from component_data
where Parent IN($(vFilterList))

If PartNo isn't numeric and/or your db requires any kind of quotes you need to add them during the concat() aggregation.

- Marcus

2 Replies
MVP & Luminary
MVP & Luminary

Re: How to pass values from one loaded table as sql parameters to another

You could try the following approach:

Parts:
load PartNo, Description, Date_created;
sql select * from part_data where Date_created = '$(vToday)';

FilterList: load distinct concat(PartNo, ',') as FilterList resident Parts:

let vFilterList = peek('FilterList', 0, 'FilterList');

Components:
load Parent as PartNo, Component, Usage;
sql select * from component_data
where Parent IN($(vFilterList))

If PartNo isn't numeric and/or your db requires any kind of quotes you need to add them during the concat() aggregation.

- Marcus

jmclaren
New Contributor II

Re: How to pass values from one loaded table as sql parameters to another

Thank you so much Marcus - that's going to be extremely useful to know that technique. As you had suggested, the only change I had to make was to change my concat statement :

from:

concat(PartNo, ',')

to:

concat(Chr(39)&PartNo&Chr(39), ',')

Jim