Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

problem in dynamic concatenation

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

Tags (1)
10 Replies
Highlighted
MVP
MVP

Re: problem in dynamic concatenation

What error message do you get ?

Try moving your dollar sign expansion between the

FileList:

and

Load

statements.

Highlighted
Not applicable

Re: problem in dynamic concatenation

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

Highlighted
Partner
Partner

Re: problem in dynamic concatenation

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.

Highlighted
Not applicable

Re: problem in dynamic concatenation

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

Highlighted
MVP
MVP

Re: problem in dynamic concatenation

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) ;

Highlighted
Not applicable

Re: problem in dynamic concatenation

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













 

Highlighted
Not applicable

Re: problem in dynamic concatenation

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

Highlighted
Partner
Partner

Re: problem in dynamic concatenation

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.

Highlighted
Partner
Partner

Re: problem in dynamic concatenation

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.