SCAN ISSUE EXPORTING QTY ON UNIQUE LIST#S 2

[eboughey1008]

I'm trying to export each unique list in a table and have them export separately with the correct qty of records displayed in the filename.

I have a field called list_no to scan into a temp table for the count qty... It's not exporting properly though. One file looks okay and the other doesn't have anything but the hard coded characters and record count (---3 recs.csv) It's not displaying the fields from t3temp AND it's assigning the filename incorrectly. The only thing right in this debacle is the record qty.

The file should export like this: "AMGA-Client Name-03_04_2023-Print Vendor-4 recs.csv" / "AMGA-Client Name-03_05_2023-Print Vendor-3 recs.csv". Any assistance at all would be much appreciated. I've tried different ways to put the scan/endscan but still didn't work. I normally just use a Do while on this because I don't need unique record qty.


lcprefix = "D:\nvatomate\jobs\retarget\"
IF NOT Directory(lcprefix)
MD (lcprefix)
ENDIF


Select Count(*) as qty, list_no FROM t3temp ;
Where EMPTY(Status);
Group By list_no;
Into Cursor curCounts

Select curCounts
SCAN
SELECT t3temp

lcNewfile = ALLTRIM(lcprefix)+ALLTRIM(dlrcode)+"-"+ALLTRIM(dealer)+"-"+ALLTRIM(MATCHDATE)+"-Print Vendor-"+ALLTRIM(STR(curCounts.qty,10,0))+" recs.csv"
COPY TO (lcNewfile) CSV FOR list_no = curCounts.list_no


ENDSCAN

CLOSE ALL
CLEAR

 

[eboughey1008]

I was able to get it working by doing the following:

1. GO TOP when I select the file (If I don't it pushes out hard coded dashes and curcounts.qty for all but 1 file.)
2. Added the list_no field which is the unique identifier from curcounts.list_no instead of t3temp.Matchdate

Such small errors can cause such a big ruckus! Have a great week all
Elena


Select curCounts
SCAN

SELECT t3temp
GO TOP

lcNewfile = ALLTRIM(lcprefix)+ALLTRIM(t3temp.dlrcode)+"-"+ALLTRIM(t3temp.dealer)+"-"+substr(curcounts.list_no,7,10)+"-"+ALLTRIM(STR(curCounts.qty,10,0))+" recs.csv"
COPY TO (lcNewfile) CSV FOR list_no = curCounts.list_no


ENDSCAN

 

[mjcmkrsr]

Hi

Select curCounts
SCAN

[highlight #73D216]SELECT t3temp
GO TOP[/highlight]

lcNewfile = ALLTRIM(lcprefix)+ALLTRIM([highlight #73D216]t3temp.dlrcode[/highlight])+"-"+ALLTRIM([highlight #73D216]t3temp.dealer[/highlight])+"-"+substr(curcounts.list_no,7,10)+"-"+ALLTRIM(STR(curCounts.qty,10,0))+" recs.csv"
COPY TO (lcNewfile) CSV FOR list_no = curCounts.list_no


ENDSCAN

Are you aware that t3temp.dlrcode and t3temp.dealer will never change and always take the field values from the 1st record? If that's what you want that's fine. However why not use local variables in this case?

hth

MarK

 

[Chris Miller]

I think the change from matchdate to list_no in the file name was the important fix, not the GO TOP. If matchdate is not unique you only get the output of the last COPY TO, of course, COPY TO does not append.

But COPY TO does not need the GO TOP. It would need a scope REST or NEXT N to start at the current record. Even if COPY TO would be a command starting at the current record by default, a FOR clause implicitly also always sets the general scope to [tt]ALL FOR condition[/tt]. But without any clause, COPY TO also starts at the top. So it's actually necessary to use one of the scopes [tt]REST FOR condition[/tt] or [tt]NEXT N FOR condition[/tt] or [tt]WHILE condition[/tt] to not start at the top.

Edit: Also see mjcmkrsr's comment on GO TOP. Make sure you have the right record to put together your filename. You should indeed put all the parts you need into the curCounts cursor, when you create it, and only change to t3temp with SELECT t3temp before the COPY TO, not before creating the output file name.

Chriss

 

[Chris Miller]

My guess is you need this, you can't just group by listno, you have to group by list no and dealer.

lcprefix = "D:\nvatomate\jobs\retarget\"
IF NOT Directory(lcprefix)
MD (lcprefix)
ENDIF

Select Count(*) as qty, list_no, dlrcode, dealer FROM t3temp ;
Where EMPTY(Status);
Group By list_no, dlrcode, dealer;
Into Cursor curGroups

Select curGroups
SCAN

lcNewfile = ALLTRIM(lcprefix)+ALLTRIM(curCounts.dlrcode)+"-"+ALLTRIM(curCounts.dealer)+"-"+substr(curcounts.list_no,7,10)+"-"+ALLTRIM(STR(curCounts.qty,10,0))+" recs.csv"

SELECT t3temp
COPY TO (lcNewfile) CSV FOR list_no = curCounts.list_no and dlrcode=curCounts.dlrcode and dealer = curCounts.dealer

ENDSCAN

CLOSE ALL
CLEAR

If your main goal is to have one CSV file per dealer the grouping would also not be done by list_no. Instead, you could output ordered by list_no, for example. But all in all your cursor curCounts should be curGroups, as I renamed it, as it has to contain all fields that actually make one group. Your file name can only contain all information of a group. If you really only want to output by list_no, then this is the only field and information you have for the file name. Not the dealer or matchdate. If you want to group by machdate, too, you'll likely have multiple files per list_no, one for each unique matchdate, but as you said matchdate isn't unique per list_no, that means multiple files per list_no.

In very short: The grouping you want in your files determines what gropuing to use in creating the curGroups cursor and the FOR condition to filter records of that group also has to contain all group fields. And vice versa, if you use less fields for grouping, you can't be sure about the uniqueness of other fields within the group and can't make them part of the filename, at least if the file name should point out that all rows within it are with the value that's put into the filename. If you make a filename like "starting from"+matchdate, then the expectation is that matchdate is the first date in that file, not the only date. You can always also do such things, but then need to determine Min(Matchadate) per group, of course.

So file naming isn't strictly bound to the constant values in a group only, but usually it is. The naming of your files was your main problem, and overwriting them, therefore.

It's important to have the FOR condition of the COPY TO match all fields that make up the group. Otherwise, the row count in the CSV will not be the Count(*) counted while determining the group sizes. You didn't error on that as you only grouped by list_no. But your file names weren't unique as list_no wasn't in the name. You have list_no in your name now, besides other information, so you never get a same file name and row counts will add up, but check whether the names are actually correct in terms of the information aside from the list_no. Because this always comes from the first record in t3temp, it is likely false for all but one file.

Chriss

 

[Chris Miller]

The most general advice on grouping data is always to group by all fields, even if some field you group by makes other fields the same value throughout all group records, too.

For example, if you join records by foreignkey=primaykey and keep the foreign key in the result, then all rows with the same foreign key value also have the same value in fields coming from the joined table, and so it seems unnecessaray to group by foreignkey and also all fields that came in from the join. But you will need to, as group by won't know this is true.

A recipe to partition your data by groups for output is this:

1. Determine group constants, the values that make up a group. That's usually multiple fields.
2. Select the data of one group by filtering for all group fields value with the data you got from step 1.

When it comes to specifying file names for the groupwise results of step 2, use the data from step 1 for the naming. And as a consequence of that, pull in all the data you need for your file naming into the group constants. But also assure they are not creating more groups than you want.

That last condition means if you have a field that varies in the group, but it's still significant to indicate it in the file name, then determine the best indicator, min, max or avg, for example. But if you add it to the list of fields you group by, you're adding the condition it isn't allowed to vary, it becomes a group constant, and therefore increases the number of groups. And that could easily get to a point where you have too many groups and too many output files. Which means it's sometimes better to make a compromise of the file name to not contain some information other than the core group constants.

Well, nd of course once aa group constant is in the file name, it doesn''t need to be part of the file content. Though it's always good to have it in to confirm this.

Chriss

 

[eboughey1008]

Chris,

You were absolutely right that it would always get the first record's dealer name! When my export bombed this morning I was gutted. I was hoping someone had responded to this post seeing my error.

Thanks for the additional information too. It really helped me understand things much better. I normally do my grouping with the "Do While" because I don't need qty of recs on export. With this knowledge I can now run those exports through the scan and add the qty to the filename. THANK YOU SOOOOO MUCH!

lcOldJobNo = filename
GO TOP
DO WHILE NOT EOF()
lcNewFile = lcprefix+ALLTRIM(filename)+"-KBB-Job "+ ALLTRIM(list_no)+"-"+ALLTRIM(datefield)+".csv"
COPY TO (lcNewFile)csv WHILE filename=lcOldJobNo
lcOldJobNo = filename

 

[eboughey1008]

Oh and thank you Mark... It did exactly what you said it would. You and Chris totally saved me this morning. I thought it was all set only to see it had bombed out after I walked away.