problem in dynamic concatenation

[#1]

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'csv'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              $(vConcatenate)

                             FileList:

                              LOAD  distinct  @3 as smsisdn,

                              (@1/86400)+25569 as stimestamp,

                               @2 as snetworkid

                                 , '$(FoundFile)' as SourceFile

                              FROM [$(FoundFile)] (txt, codepage is 1252, no labels, delimiter is ',');

                              Actual:

                              load smsisdn, max(stimestamp) as sTimestampMax, firstsortedvalue(snetworkid, stimestamp) as snetworkid

resident FileList

group by  smsisdn;

//drop table FileList;

                              Set vConcatenate = NoConcatenate;

                    next FoundFile

          next FileExtension

end sub

Call ScanFolder(path) ;

*/

I am getting error in the highlighted part and script execution is failed. please help me how to resolve this

[swuehl]

What error message do you get ?

Try moving your dollar sign expansion between the

FileList:

and

Load

statements.

[#3]

i tried that too, but i am not able to get it. getting syntax error

[christian77]

Hi:

Try to add a different field on each loop. Erase Concat var from your loop. Make a field and rename it $(FoundFile).

You may drop those fields at the end of your script.

Salutes.

[#5]

but my problem is i want to get the data in a single file.

msisdn     timestamp       networkid.

only these fields, i want in my output with new table appended to old one

[swuehl]

Why do you want to NoConcatenate Load your FileList files? This will create several tables with names like

FileList, FileList-1, FileList-2, etc. Your following Load for Actual table will always adress the same table FileList then.

Why not try your drop Table FileList approach without the vConcatenate variable.

//Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'csv'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

//                              $(vConcatenate)

                             FileList:

                              LOAD  distinct  @3 as smsisdn,

                              (@1/86400)+25569 as stimestamp,

                               @2 as snetworkid

                                 , '$(FoundFile)' as SourceFile

                              FROM [$(FoundFile)] (txt, codepage is 1252, no labels, delimiter is ',');

                              Actual:

                              load smsisdn, max(stimestamp) as sTimestampMax, firstsortedvalue(snetworkid, stimestamp) as snetworkid

resident FileList

group by  smsisdn;

drop table FileList;

//                             Set vConcatenate = NoConcatenate;

                    next FoundFile

          next FileExtension

end sub

Call ScanFolder(path) ;

[#7]

vConcatenate = ;

Sub DoDir (Root)  

     For Each Ext In 'csv' // filetype to search for in current directory  

         For Each File In FileList (Root & '\*.' & Ext)  

                  Files:  

                   Load '$(File)' as Name,  

                     FileTime('$(File)') as FileTime,  

                    RangeSum(Peek('FileCount'), 1) as FileCount  

                  

                   AutoGenerate 1;

          Next File  

     Next Ext  



   

 

End Sub  

 

Call DoDir ('D:\starhub\sample'); // Starting point  

 

 

 



let vCounter=FieldValueCount('FileCount');



let i=1;

let j=1;



Sub ReadFile (Root)  

     For Each Ext In 'csv' // filetype to search for in current directory  

         For Each File In FileList (Root & '\*.' & Ext)

         

                  Report$(i):

LOAD



distinct  @3 as msisdn$(i),

(@1/86400)+25569 as timestamp$(i),

     @2 as networkid$(i)

  

FROM

[$(File)] (txt, codepage is 1252, no labels, delimiter is ',');

i=i+1;

  next File



          next Ext



For Each Ext1 In 'csv' // filetype to search for in current directory  

         For Each File1 In FileList (Root & '\*.' & Ext1)

         

Actual:

$(vConcatenate)



//grouping to get the first record of user in a networkid



load msisdn$(j) as msisdn, max(timestamp$(j)) as TimestampMax, firstsortedvalue(networkid$(j), timestamp$(j)) as networkid,

$(j) as feedid

resident Report$(j)

group by  msisdn$(j);





inner join



//mapped with lat and long based on networkid

LOAD @1 as networkid,

     @2 as lat,

     @3 as long

FROM



(txt, codepage is 1252, no labels, delimiter is spaces, msq);



drop table Report$(j);

j=j+1;

   Set vConcatenate = Concatenate;

 

next File1



          next Ext1



End Sub  



 

Call ReadFile ('D:\starhub\sample'); // Starting point  



My actual code is this

now i want to get single Actual table, with all the data in report tables appended to it













 

[#8]

i am getting three tables(as i am loading from three files) in the above case if i dont use concatenate. But i want to get only single table at output

[christian77]

hi again:

If your files have the same structure, QlikView will concatenate them automatically. If they don't have the same structure and you want them together, don't say NOConcatenate, say concatenate.

1 loop. Noconcatenate? Does it have same structure that other table? if not, you don't need to use NoConcatente.

2 and next loops. concatenate(FileList)

That happens many times.

I repeat the question: Do they have the same structure? if yes, place the piece of script at the first script page and QV will do the job right and naturally.

[christian77]

Ok, use a count(FileList) and build a loop like for i=0 to count(FileList). when i=0 Noconcatente, else concatenate.

To retrieve the file name use th function FileName.

If there is not a count file list, make your own counter with a variale.

set Var =0; before that loop.

set Var = Var +1; on each iteration.

Place that at the end or beginning of the loop to your convenience.

luck.