Concatenate question

[#1]

Hello,

I have an issue that hopefully you can help me with. I'm loading about 100 files from a folder using this automated script:

For each vFile in FileList('t:\CONTABILITATE\Detaliutichete*.dbf')

QUALIFY *;

$(vFile):

load

  if(left(right(filebasename(),6),1), 1, 0) &

  left(right(filebasename(),5),1) as cifra2,

  date(date#(right(filebasename(),4), 'YYYY'),'YYYY') as an

FROM $(vFile);

join

$(vFile):

  SQL SELECT *

FROM $(vFile);

next vFile;

First part creates 2 fields from the file name (month and year) while the second part loads the existing fields from the tables .

Any idea how I can concatenate the files?

[hic]

First you need to remove the Qualify statement. Concatenation is impossible if the field names are different.

Further - if I understand you correctly, the load statement will just create some date information from the file name? If so, you should instead use a preceding Load:

For each vFile in FileList('t:\CONTABILITATE\Detaliutichete*.dbf')

$(vFile):

load

  if(left(right(filebasename(),6),1), 1, 0) &

  left(right(filebasename(),5),1) as cifra2,

  date(date#(right(filebasename(),4), 'YYYY'),'YYYY') as an ;

SQL SELECT * FROM $(vFile);

next vFile;

HIC

[hic]

I just realized that your call to "filebasename()" will not work either. So you need to replace these in the above script with "Subfield(Subfield('$(vFile)','\',-1),'.',1)"

HIC

[#4]

Hello Henric,

Thank you for your response. The statement does not work without the qualify statement. If I do not use the qualify statement, I cannot load the specific date and month from each file. But, as you said, qualify keeps the tables from concatenating.

Filebasename() actually works with my first load, it creates the data in the fields.

Would I be able to use the subfield function without a delimiter? My files look like this: detaliutichete12004, detaliutichete22004... detaliutichete102004 etc.

Thanks in advance

[hic]

Subfield will not work without delimiter. But Left() and Mid() will. So Mid('detaliutichete22004',15) will return '22004'

HIC

[jonathandienst]

Hi

The problem is trying to do a join in a loop. After the first pass through the loop, the extra fields have already been created and so the join does not work properly in subsequent passes. Qualify "fixes" this, but is not the correct solution.

The way I solve this sort of problem is to load and join in a temp table (T_Data) in the loop, conatenate the temp table to the result table (Data), and drop the temp table for the next pass. In your case, this could look like:

For each vFile in FileList('t:\CONTABILITATE\Detaliutichete*.dbf')
          T_Data:
          load
            if(left(right(filebasename(),6),1), 1, 0) &
            left(right(filebasename(),5),1) as cifra2,
            date(date#(right(filebasename(),4), 'YYYY'),'YYYY') as an
          FROM $(vFile);
          join
          $(T_Data):
            SQL SELECT *
          FROM $(vFile);
          If NoOfRows('Data') > 0 Then
                    Concatenate(Data)
                    LOAD * Resident T_Data;
          Else
                    Data:
                    NoConatenate
                    LOAD * Resident T_Data;
          End If
          DROP Table T_Data;
next vFile;

For the first pass, we must use NoConcatenate to prevent auto concatenation into T_Data. On subsequent passes we explicitly concatenate the temp table tto the result table.

You could ptimise this further by getting the date information from vFile which would save opening the files

Hope that helps

Jonathan

[jonathandienst]

But I think you can avoid the join completely using the preceding load as suggested by Henric:

For each vFile in FileList('t:\CONTABILITATE\Detaliutichete*.dbf')
     Let vFileBaseName = SubField(Subfield(vFile, '\', -1), '.', 1);
     Data:
     LOAD *,
          if(left(right(vFileBaseName,6),1), 1, 0) & left(right(vFileBaseName,5),1) as cifra2,
          date(date#(right(vFileBaseName,4), 'YYYY'),'YYYY') as an;
     SQL SELECT *
           FROM $(vFile);
Next vFile;

This gets the file information from vFile

Regards

Jonathan

[#8]

Hello Jonathan,

Thank you for your interest in this. Your method seems promising however I cannot get it to work. The second example brings nothing while the first one has a script error in this portion.

Else

                    Data:

                    NoConatenate

                    LOAD * Resident T_Data;

I seem to be lost,your help is much appreciated.

Thank you

*** I was not going through because of a spelling mistake (NoConcatenate). The script still doesn't bring any values

[#9]

The error I am getting is

Field not found - <vFileBaseName>

Any ideas?

[hic]

use

     '$(vFileBaseName)'

with single quotes instead of

     vFileBaseName

HIC