Data loading based on filename

[Deepak_dhikale]

Hi all.

I am new here.

I just want to separate the data load script based on filename. As I have files for 3 day and 7 day .i just want to make separate load script and separate qvd for weekly_3_day_rates and weekly_7_day_rates . As all files contains same no of rows and column's I am fetching data from SFTP server.

FilesAndFolders:
LOAD
Name,
Path,
IsDirectory,
Size,
SubField(SubField(Name,' ',1),'_',2) as newfile

FROM [lib://sftp_url (allenlund_nprinting)]

(URL is [http://qliksense:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&host=harm...);

LET vNoOfFiles = NoOfRows('FilesAndFolders');

with the above script i get below output

please help me solve this.

Thank you _)

[Taoufiq_Zarra]

@Deepak_dhikale  can you elaborate

can you share a sample data and the expected output ?

[Gysbert_Wassenaar · Accepted solution]

Perhaps something like this:

// create empty table for 3 day rates
[weekly_3_day_rates]:
NOCONCATENATE LOAD 0 as rate autogenerate 0 // it's assumed the result table will have a field called 'rate', adjust as needed

// create empty table for 7 day rates 
[weekly_7_day_rates]:
NOCONCATENATE LOAD 0 as rate autogenerate 0 // it's assumed the result table will have a field called 'rate', adjust as needed

For i = 1 to NoOfRows('FilesAndFolders') -1

	LET vFileName = peek('Name', $(i) , 'FilesAndFolders');
	LET vIsDir = peek('IsDirectory', $(i) , 'FilesAndFolders');
	LET vFreq = peek('newfile', $(i) , 'FilesAndFolders');
	
	IF vIsDir = 1 THEN 

		// it's a directory not a file so skip it

	ELSE
	
		//Change LIB://MyCSVLocation to whatever connection you use that points to where your csv files are stored
		tmpData:
		NOCONCATENATE LOAD * FROM [LIB://MyCSVLocation/$(vFileName)] (txt, ...etc)

		CONCATENATE ([weekly_$(vFreq)_day_rates])
		LOAD * FROM tmpData;

		DROP TABLE tmpData;
		
	END IF

   //Change LIB://MyQVDLocation to whatever connection you use that points to where your qvd files are to be stored
	STORE [weekly_3_day_rates] INTO [LIB://MyQVDLocation/weekly_3_day_rates.qvd] (qvd);
	STORE [weekly_3_day_rates] INTO [LIB://MyQVDLocation/weekly_7_day_rates.qvd] (qvd);
		
Next