Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jmbenedetto
Employee
Employee

Using string functions with variables outside load

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;

Labels (1)
5 Replies
vinieme12
Champion III
Champion III

try replacing 

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

with below

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
jmbenedetto
Employee
Employee
Author

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
Champion III
Champion III

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

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
jmbenedetto
Employee
Employee
Author

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