topic Using string functions with variables outside load in App Development

Using string functions with variables outside load

jmbenedetto — Fri, 28 Oct 2022 18:04:47 GMT

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;

Re: Using string functions with variables outside load

vinieme12 — Mon, 31 Oct 2022 04:08:06 GMT

try replacing

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

with below

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

Re: Using string functions with variables outside load

jmbenedetto — Mon, 31 Oct 2022 13:27:02 GMT

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

Re: Using string functions with variables outside load

vinieme12 — Mon, 31 Oct 2022 15:41:04 GMT

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),'_','.')

Re: Using string functions with variables outside load

jmbenedetto — Tue, 01 Nov 2022 11:55:30 GMT

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

Re: Using string functions with variables outside load

marcus_sommer — Tue, 01 Nov 2022 13:55:34 GMT

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