topic Re: How to extract iSeries data from a specific file member in QlikView

How to extract iSeries data from a specific file member

Wed, 09 Dec 2009 13:47:10 GMT

I am trying to retrieve data from a specific member from a file on the iSeries.
My SQL statement:

Select * From LIBRARY.FILE(MEMBER)

does not work.
Excluding the member works but only retrieves records from the first member ie

Select * From LIBRARY.FILE

In our case the first member's name is the same as the filename
can anyone help me please?

Anonymous — Mon, 22 Aug 2011 13:36:58 GMT

you should create a logical file on AS400 (CRTLF) specifying the desired member.

then use the logical filename in your sql statement

jpengiu — Fri, 02 Dec 2011 08:27:57 GMT

The best way for me is create an ALIAS with SQL on iseries:

CREATE ALIAS ALIAS_NAME FOR PFILE_NAME(PFILE_MEMBER)

Then on Qlik you have use ALIAS_NAME

Thu, 17 Jan 2013 15:31:02 GMT

hope this can help u is what i use

i have a table containing periods (YYYYMM) as name of the member in the file but u can use whatever list of MBR

contained in the file

for vTemp= 0 to peek('PeriodKey',-1,'tblPeriodoCompleto')-1; (for all periods in the table)

let PeriodToLoad = peek('PeriodKey',$(vTemp),'tblPeriodoCompleto'); (i identify the single period to load)

tblDetailedPeriods:

SQL CREATE ALIAS $(LibTemp).TEMPMBRNAME FOR $(LibraryName).FILENAME00F ($(PeriodToLoad));

insert here Load or Select u need from $(LibTemp).TEMPMBRNAME

SQL DROP ALIAS $(LibTemp).TEMPMBRNAME;

i use some variables to reuse code but is ok

martynlloyd — Wed, 14 Jun 2017 11:06:24 GMT

Dear Andrea,

FANTASTIC! Thanks very much, I have been trying to solve this for weeks.

Kind regards,

Marty.