Discussion board where members can learn more about Qlik Sense App Development and Usage.
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;
try replacing
TextBetween(Mid('$(File)',Len('$(pDataSourcePath)')+2),'_','.') as DestinationTable
with below
TextBetween(Mid('$(File)',index('$(pDataSourcePath)','\',-1),'_','.') as DestinationTable
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
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),'_','.')
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
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