topic Re: problem in dynamic concatenation in QlikView

problem in dynamic concatenation

Fri, 11 Oct 2013 08:37:49 GMT

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

Re: problem in dynamic concatenation

swuehl — Fri, 11 Oct 2013 08:56:35 GMT

What error message do you get ?

Try moving your dollar sign expansion between the

FileList:

and

Load

statements.

Re: problem in dynamic concatenation

Fri, 11 Oct 2013 08:59:32 GMT

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

Re: problem in dynamic concatenation

christian77 — Fri, 11 Oct 2013 09:16:37 GMT

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.

Re: problem in dynamic concatenation

Fri, 11 Oct 2013 09:20:22 GMT

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

Re: problem in dynamic concatenation

swuehl — Fri, 11 Oct 2013 09:53:04 GMT

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

Re: problem in dynamic concatenation

Fri, 11 Oct 2013 09:58:33 GMT

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

Re: problem in dynamic concatenation

Fri, 11 Oct 2013 10:50:30 GMT

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

Re: problem in dynamic concatenation

christian77 — Fri, 11 Oct 2013 11:35:07 GMT

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.

Re: problem in dynamic concatenation

christian77 — Fri, 11 Oct 2013 11:47:27 GMT

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.

Re: problem in dynamic concatenation

swuehl — Fri, 11 Oct 2013 11:48:33 GMT

I think the inner join of your lat / long table might prevent the actuals table from proper concatenating.

Try to join the table at the very end of your sub, after you've concatenated all actual tables.