HI,
I am trying to read distinct COUNTRY values from CAR file one by one in a loop using below code:

-SET &ECHO = ON;
-SET &ECHO = ALL;

TABLE FILE CAR
PRINT
DST.COUNTRY 
BY COUNTRY
ON TABLE HOLD AS HLD
END
-RUN

-SET &RECS=&LINES;
-TYPE &RECS


-REPEAT LP &RECS TIMES

-READ HLD &COUNTRY.A50.
-TYPE &COUNTRY
-*
-SET &PARAM2 = TRUNCATE('&COUNTRY.EVAL');
-TYPE &PARAM2

FILEDEF BURSTPARAM DISK ABC/BURSTPARAM.TXT
-RUN
-WRITE BURSTPARAM &COUNTRY
-LP
  

I have to write the country value in BURSTPARAM .TXT and read this value in other procedure in loop as shown in above code.
When I am trying to WRITE the value in the file BURSTPARAM.TXT using FILEDEF , it only picks first value from the hold file HLD for each iteration.

When I run this code after removing FILEDEF and WRITE command, it runs for each of the country code.
Also, is there any timing issue for each iteration. i.e if second proc picks value ENGLAND and in the meantime the BURSTPARAM.TXT get overwritten and it may cause

Please help me understand the behaviour and cause of this issue.

Thanks.

This message has been edited. Last edited by: <Emily McAllister>,

Try this

-SET &ECHO = ON;
-SET &ECHO = ALL;

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS HLD
END
-RUN

-SET &RECS=&LINES;
-TYPE &RECS

FILEDEF BURSTPARAM DISK ABC/BURSTPARAM.TXT
-RUN

-REPEAT LP &RECS TIMES
-READFILE HLD
-TYPE &COUNTRY
-SET &PARAM2 = TRUNCATE('&COUNTRY.EVAL');
-TYPE &PARAM2

-WRITE BURSTPARAM &COUNTRY
FILEDEF BURSTPARAM DISK ABC/BURSTPARAM.TXT (APPEND
-RUN
-LP

The first FILEDEF clear the file, the second insure to APPEND data to it. Having the FILEDEF as you did always clear the file before each WRITE.
Also, using READFILE instead of READ allows you to discard the field length and with your BY and PRINT, your result in HLD id two columns, not one.

quote:

COUNTRY

Hi Martin,
Thanks for your reply.
As this logic creates a file like below:

ENGLAND
FRANCE
ITALY
JAPAN
W GERMANY

The problem is that I wont be able to identify which value to pick in my second proc which basically uses only one value used in that iteration.
i.e For ENGLAND iteration the second proc will use ENGLAND in its logic. similarly for others.

Please suggest.

Thanks.

Hi Shankar,

Maybe it's me but I don't understand what you are looking for...

What do you mean by "For ENGLAND iteration the second proc will use ENGLAND in its logic" ?

Is that you want to execute another fex (or TABLE FILE) looping on the values contained in the FILEDEF ?
So meaning that you'll loop five times (one time for each COUNTRY values) ?

To grab every individual record from a held data request, you could do something similar to the following. Add a record counter to the end of the DM variables you're trying to create and store values in. Then you end up with DM variables that hold every value for every field from every record that comes back from the above data request. This can be a handy way to gather values from a data set to use later in custom HTML for widgets, etc. you might like to build for a dashboard/portal, etc. Hope this helps.

... Data request...
ON TABLE HOLD
...


-SET &I = 0;
-LOOP
-SET &I = &I + 1;
-IF &I GT 5 GOTO OUT;
-READ HOLD &FIELDONE.&I.A35. &FIELDTWO.&I.A35. &FIELDTHREE.&I.A35. &FIELDFOUR.&I.A35.
-TYPE &FIELDONE.&I &FIELDTWO.&I &FIELDTHREE.&I &FIELDFOUR.&I
-GOTO LOOP
-OUT

quote:

something

Hi,
Yes Martin. I want to execute another fex by looping five times for each country values. It will read from FILEDEF file created to hold COUNTRY values.
I was thinking to overwrite the COUNTRY values for each iteration so that each iteration will pick the latest value to execute the other fex file.

I think overwriting the values in FILEDEF file will help here.
Please suggest.

Thanks.

No need to use FILEDEF for that

-SET &ECHO = ON;
-SET &ECHO = ALL;

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS HLD
END
-RUN

-SET &RECS=&LINES;
-TYPE &RECS

-SET &I = 0;
-NEXTCNTRY
-SET &I = &I + 1;
-READFILE HLD
-TYPE &COUNTRY

TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ '&COUNTRY';
END
-RUN

-IF &I LT &RECS THEN GOTO NEXTCNTRY;

Hi Martin,
Actually, the FILEDEF file country values will be used in the fex which is called through RC Schedule.
The parent fex (where the COUNTRY values is identified and written into FILEDEF) will then trigger this RC jobs which will execute the child fex which would use the parameter value in FILEDEF file.

It is just a way to pass parameter from one task to other in loop for each COUNTRY values.
Please advise.

Thanks.

Try this:

APP HOLD BASEAPP
END

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS BURSTPARAM FORMAT ALPHA
END
-RUN

-SET &RECS=&LINES;

APP HOLD
END

-SET &I = 0;
-NEXTCNTRY
-SET &I = &I + 1;
FILEDEF BURSTPARAM DISK baseapp/burstparam.ftm
-READFILE BURSTPARAM

TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ '&COUNTRY';
END

-IF &I LT &RECS THEN GOTO NEXTCNTRY;

Pay attention to not put any -RUN after the READFILE neither the TABLE FILE...END of the loop otherwise you will have five times the ENGLAND report : &COUNTRY will remain the same. In other words, do not put any -RUN in the loop.

I have a fex - nearly 900 lines long - that does what you want - exactly along the lines that Martin has suggested. In my case I needed to create a spreadsheet with tabs for months along the bottom, so I couldn't use the first BY field as the burst parameter.

We still use ReportCaster to schedule the job, but EDAMAIL for the actual distribution.

Of course this method is somewhat inefficient - the entire fex gets run for each individual sales rep - but it works.

-INCLUDE FILEDEFS
-DEFAULTH &SALESREP=' ';
 
APP HOLD BASEAPP
 
-* Get a list of the sales reps and count how many their are
 
TABLE FILE SALES_TARGS
SUM SALESREP
BY SALESREP NOPRINT
ON TABLE HOLD AS ALLREPS FORMAT ALPHA
END
 
-SET &NUMREPS=&LINES;
-RUN
 
-READFILE ALLREPS
-* Note that READFILE converts the FOCUS fieldname to the Dialog Manager fieldname &SALESREP

-REPEAT ENDREPEAT &NUMREPS TIMES
 
-SET &SUBJECT='Targets and Sales for ' | &SALESREP | ' ' | &REPDATE;
 
-SET &SREP_EMAIL=LOCASE(3, &SALESREP, 'A3') || '@xxx.com';
 
-SET &ALL_EMAILS=&SREP_EMAIL || ';' | &ADMIN_EMAILS; 

-* 800+ lines of code in here... 

TABLE FILE XYZ
...
...
WHERE SALESREP EQ '&SALESREP'
END
 
EX EDAMAIL &ALL_EMAILS, ReportCaster@xxx.com, &SUBJECT,a,XLSX,baseapp/Targets and Sales for &SALESREP &REPDATE
-RUN
READFILE ALLREPS
ENDREPEAT
-RUN
 
TABLE FILE H001
SUM DUMMY NOPRINT
BY CCODE NOPRINT
HEADING
"Targets and Sales Reports completed"
END
 
-DONE

This message has been edited. Last edited by: George Patton,

Shankar, you gotta hold as FORMAT ALPHA
you're holding as a binary, so a -READ has a hard time. That's what a SAVE is, its a HOLD FORMAT ALPHA w/o the master.

plus you gotta put a NOCLOSE after your -READ so the fex doesn't keep going to the top of the extract file.

quote:

BURSTPARAM

Hi,
The parent and child tasks needs to be executed simultaneously. i.e. If Task1 uses ENGLAND value then task2 should also use ENGLAND value for that particular iteration.
Both the tasks are tightly coupled here.

I am just not able to understand, When Task1 loops through different country values and use that value for its execution, how can I pass the same value to Task2 to complete its execution simultaneously. Then it will move to next country value and execute both the tasks and similarly for all the country values.

Please suggest.

Thanks.

There have been many suggestions. Please post the latest version of your code.

You can put as many Task as you want in the loop

APP HOLD BASEAPP
END

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS BURSTPARAM FORMAT ALPHA
END
-RUN

-SET &RECS=&LINES;

APP HOLD
END

-SET &I = 0;
-NEXTCNTRY
-SET &I = &I + 1;
FILEDEF BURSTPARAM DISK baseapp/burstparam.ftm
-READFILE BURSTPARAM

-* TASK 1
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
BY CAR
WHERE COUNTRY EQ '&COUNTRY';
END

-* TASK 2
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ '&COUNTRY';
END

-IF &I LT &RECS THEN GOTO NEXTCNTRY;

You may need to be more clear if it doesn't answer your need

Martin is right (as usual): In my example I indicated that I have more than 800 lines of code inside the loop that reference all kinds of variables, but the main one is the &SALESREP that I use to pull data from several different sources and combine into a single report.

Thanks George, but I'm not always right.
The fact is when I think that I may be wrong, I shut up