topic Re: Concatenate question in QlikView

Concatenate question

Fri, 01 Mar 2013 08:27:26 GMT

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?

Re: Concatenate question

hic — Fri, 01 Mar 2013 08:38:51 GMT

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

Re: Concatenate question

hic — Fri, 01 Mar 2013 08:45:36 GMT

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

Re: Concatenate question

Fri, 01 Mar 2013 09:14:00 GMT

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

Re: Concatenate question

hic — Fri, 01 Mar 2013 09:24:22 GMT

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

HIC

Re: Concatenate question

jonathandienst — Fri, 01 Mar 2013 09:46:33 GMT

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

Re: Concatenate question

jonathandienst — Fri, 01 Mar 2013 09:48:02 GMT

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

Re: Concatenate question

Fri, 01 Mar 2013 10:57:29 GMT

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

Re: Concatenate question

Fri, 01 Mar 2013 15:29:18 GMT

The error I am getting is

Field not found - <vFileBaseName>

Any ideas?

Re: Concatenate question

hic — Fri, 01 Mar 2013 15:35:55 GMT

use

'$(vFileBaseName)'

with single quotes instead of

vFileBaseName

HIC

Re: Concatenate question

Fri, 01 Mar 2013 17:01:05 GMT

Hello Henric,

I think your solution with the preceding load will work best. The only piece missing from this puzzle is the name of the file. You were right, filebasename() only fires if I use the statement from the first post.

If I use preceding load, filebasename() stops firing.

For each vFile in FileList('t:\CONTABILITATE\Detaliutichete*.dbf') Let vFileBaseName = filebasename(); Data: LOAD *, '$(vFileBaseName)' as an; SQL SELECT * FROM $(vFile); Next vFile;

Subfield is out of the question I presume because my filenames do not have a delimiter.

So what can I use instead of filebasename that will give me the same thing?

Re: Concatenate question

hic — Fri, 01 Mar 2013 17:17:41 GMT

I think you're almost there. If you use

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

Let vFileBaseName = SubField(Subfield(vFile, '\', -1), '.', 1);

Let vFileNumber = KeepChar(vFileBaseName,'0123456789');

Data:

LOAD *,

'$(vFileNumber)' as an;

SQL SELECT *

FROM $(vFile);

Next vFile;

Then you will indeed load all files with the number in the field "an".

You should use Subfield to get vFileBaseName, but you cannot use subfield to just get the number. But you can get the number by using Mid() or KeepChar().

HIC

Re: Concatenate question

Fri, 01 Mar 2013 18:04:09 GMT

Henric,

This is gold. I've been struggling with this for quite some time now, but with your help I managed to make it work. I'm in your debt. Thanks a million

Bogdan