Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What error message do you get ?
Try moving your dollar sign expansion between the
FileList:
and
Load
statements.
i tried that too, but i am not able to get it. getting syntax error
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.
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
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) ;
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
(
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
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
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.
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.