Create and Populate Values to An Array and Use that Array inside For Loop

[muhammedraleen]

Hi All,

I have query which outputs just only 4 records. I need those 4 rows to be saved into a variable. That variable I need to use inside for loop. Where based on that 4 records I need to run SQL scripts. I can load manually all those 4 tables, but i need to automate for avoiding future reworks. 

Example:- 

Query needs to run is SQL select TRANSID from grnhdr; This outputs 4 records as of now, which are A,B,C,D. Now I need to load workflow table of these, where it's table name is AWORKFLOW,BWORKFLOW,CWORKFLOW & DWORKFLOW. Likewise, each transid has a workflow table. I need to load all the tables, in a automate process.

Purpose:- In future if they add a new transid, that new transid's workflow also needs to be loaded.

How is it possible ?

[ManuelRühl · Accepted solution]

I use something like that for generating several tables in a loop:

Workflowtable:

LOAD

    rowno() as Counter,

    TRANSID&'WORFLOW' as Connect

FROM TABLE / SQL / DATABASE

FOR i=1 to FieldValueCount('Counter')

let vFieldValueCount = FieldValueCount('Counter');

let vConnect = FieldValue('Connect',$(i));

let vTabelle = FieldValue('Tabelle',$(i));

let vBezeichnung = FieldValue('Bezeichnung',$(i));

[$(vBezeichnung)]:

SQL SELECT * FROM $(vConnect);

STORE [$(vBezeichnung)] INTO .............. (QVD);

DROP TABLE [$(vBezeichnung)];

next;

[muhammedraleen · Accepted solution]

Hi @ManuelRühl ,

Thank you very much for your time and support.

Hi All,

I used the below script to achieve the final output;

T1:

SQL select distinct TRANSID from grnhdr;

let vTBName = 'workflow';

for i=1 to NoOfRows('T1')-1

let vTransId = Peek('TRANSID',$(i),'T1');

    GRNWORKFLOW:

   SQL select RECORDID,APP_LEVEL,APP_DATETIME from $(vTransId)$(vTBName);   

next

Store GRNWORKFLOW into [lib://KSPA (win-0geimupg8c8_administrator)/GRNWORKFLOW.qvd](qvd);

Drop table GRNWORKFLOW ,T1;