Using string functions with variables outside load

[jmbenedetto]

Hello everyone,

I want to load all qvds from a folder but keeping different names for each. My first idea was to use for each filelist, extract the table name from each file name and load the file from within the loop. However, I could not make it work. No string function would work. The resulting variable that should have the table name would be empty.

In the end, I came up with the sub below, where the for each would generate a table with the names, and another for loop would use the values to load the files.

Have any of will used string functions on scripts but not within load instructions?

SUB fLoadQvdsWithPrefix(pDataSourcePath,pPrefix);

/**********************************************************

The sub loads all QVDs in pDataSourcePath which start with pPrefix.
Tables will be named after the qvd, excluding prefix
defined by pPrefix.
pDataSourcePath should not include the last /.

***********************************************************/

TRACE **********************************************************;
TRACE Starting fLoadQvdsWithPrefix sub execution.;
TRACE **********************************************************;

FOR EACH File in filelist('$(pDataSourcePath)/$(pPrefix)*.qvd')
[File]:
LOAD
'$(File)' as SourceFile,
TextBetween(Mid('$(File)',Len('$(pDataSourcePath)')+2),'_','.') as DestinationTable
autogenerate 1;
NEXT File

LET vPrefixNbOfChars= Len($(pPrefix));

LET vNoOfFiles=NoOfRows('File');

TRACE **********************************************************;
TRACE #files $(vNoOfFiles);
TRACE **********************************************************;

FOR i=0 to $(vNoOfFiles)-1;
LET vSourceFile = Peek('SourceFile',$(i),'File');
LET vDestinationTable = Peek('DestinationTable',$(i),'File');
TRACE source: $(vSourceFile) table:$(vDestinationTable);
[$(vDestinationTable)]:
NoConcatenate
LOAD * FROM [$(vSourceFile)] (qvd);
NEXT i;

DROP TABLE [File];
TRACE **********************************************************;
TRACE Completed fLoadQvdsWithPrefix sub execution.;
TRACE **********************************************************;

END SUB;

[vinieme12]

try replacing 

TextBetween(Mid('$(File)',Len('$(pDataSourcePath)')+2),'_','.') as DestinationTable

with below

TextBetween(Mid('$(File)',index('$(pDataSourcePath)','\',-1),'_','.') as DestinationTable

[jmbenedetto]

HI, @vinieme12 . Thanks for your reply. The code I posted work but I need to do a support table for it.

I wanted to do something like this:

Let vDestinationTable = TextBetween(Mid('$(File)',index('$(pDataSourcePath)','\',-1),'_','.');

However, it did not work. No string function worked.

Would you have any idea why?

Best regards,

Benedetto

[vinieme12]

Is table [File] generated correctly?

Remove single quotes from the $(File) variable

Trace this 

FOR EACH File in filelist('$(pDataSourcePath)/$(pPrefix)*.qvd')

Let vDest=TextBetween(Mid($(File),index('$(pDataSourcePath)','\',-1),'_','.');

Trace $(File);

Trace $(vDest);

[File]:

LOAD

$(File) as SourceFile,

TextBetween(Mid($(File),index('$(pDataSourcePath)','\',-1),'_','.') as DestinationTable

autogenerate 1;

NEXT File

Alternatively you can also try using filename()

TextBetween(Mid(filename(),index($(pDataSourcePath),'\',-1),'_','.')

[jmbenedetto]

Hi, @vinieme12 .

Thanks again for your time.

1) I have no problem to create the table with the original code.

2) If I use my code without the single quotes around $File, I get an error message as the variable is not interpreted as a string (see first image).

3) I tried to use your suggested line

Let vDest=TextBetween(Mid($(File),index('$(pDataSourcePath)','\',-1),'_','.');

instead of

TextBetween(Mid('$(File)',Len('$(pDataSourcePath)')+2),'_','.')

However, the last dot appears in red, as if there is a syntax error.

I'm open to try any other idea.

Best regards 

[marcus_sommer]

I'm not sure that I could really comprehend your approach but I would probably use subfield() to pick the wanted string-part, like: subfield('$(File)', '/', -1) to fetch the complete filename from the string.

Another approach might be to query the meta-data of the qvd like: qvdtablename('$(File)')

- Marcus