Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I'm trying to load data from an Iseries.
And every bookyear is in another Company and also an other filepath(accesspath)
I have an excel with Year and CompanyNumber.
Boekjaar, BoekLib
2011 11E
2012 12E
2013 13E
2014 14E
I have only need to refresh the last 2 years (current year and CurrentYear -1)
Now I need to do a select like this
select *
FROM S0678ADT.X62FIL13EO.PBP;
and
Select *
FROM S0678ADT.X62FIL14EO.PBP;
Here is an example of my script where my BoekLib (in Yellow) is empty...
How can I fill up "BoekLib"?
Regards,
Kris
You used for your load within the loop the functionality that qv automatically concatenated tables which have the same fields. Is there any difference between the tables qv create a new table with a number-suffix.
In this case you should check your data and if they are correct you need a forced concatenate:
for vFileNo ...
let vConcatenate = if($(vFileNo) = 1, '', 'concatenate(MyTable)');
....
MyTable:
$(vConcatenate)
Load ....
SQL ....
next
- Marcus
Marcus,
thanks for you respond.
As I go and check the files there is indeed a difference between the files.
I had also found a solution that was working.
Lib:
LOAD
Boekjaar,
BoekLib
FROM
[..\01 Table Files\Boekhoudjaren.xlsx]
(ooxml, embedded labels, table is Blad1) ;
PBPTemp:
load * inline [
Temp
1
];
For vFileNo = 1 to NoOfRows('Lib')
LET TMP = Peek('BoekLib',$(vFileNo)-1,'Lib');
LET vYear = Peek('Boekjaar',$(vFileNo)-1,'Lib');
Let vFileName = 'S0678ADT.X62FIL' & '$(TMP)' & 'O.PBP';
Concatenate(PBPTemp)
PBPTemp:
Load *,
'$(vYear)' as Year;
SQL SELECT *
FROM $(vFileName) ;
Next vFileNo
Drop Table Lib;
NoConcatenate
PBP:
LOAD
BPKODE,
BPINTR,
BPHOVE,
BPFILL,
Year
Resident PBPTemp where Temp <> 1 ;
Drop Table PBPTemp ;