Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jmclaren
Contributor III
Contributor III

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
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

2 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

jmclaren
Contributor III
Contributor III
Author

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