Concatenated String as a Variable

[m_perreault]

Hi All,

I am looping through a folder in which the naming convention of files is structured.

1_filename.xlsx

2_filename.xlsx

3_filename.xlsx

4_filename.xlsx

I only want to load certain files from the folder so right now I have a conditional statement

For Each File in FileList('lib://Qlik Control Data/*.xlsx')

// create conditional statement to only load 1_filename.xlsx and 2_filename.xlsx

If Match(Right(Subfield('$(File)','_',1),4),'1','2') Then

LOAD

*

FROM ['$(File)']

(ooxml, embedded labels, table is CASHFLOWS)

;  

End If;

Next File;

This works well except for the fact I had to hard code 1 and 2 into my match statement.

What I would prefer to do is store 1,2 as a variable but I am unsure how to.  Prior to my loop, a table in the script is generated which identifies 1_filename.xlsx and 2_filename.xslx as the files that we want to be loaded. I.e.

FileName	Include	Prefix
1_filename.xlsx	Y	1
2_filename.xlsx	Y	2
3_filename.xlsx	N	3
4_filename.xlsx	N	4

Using this table how could I create a variable in the script that stores the value '1,2'

Thanks!

Mark

[m_perreault]

Hi All,

So I've gotten to this point

I've eliminated records where Include = N from my table and created the below loop

Let vFilePrefix = FieldValueCount('Prefix')

Do While i <= '$(vFilePrefix)'

let i = 1;

let vTest = FieldValue('Prefix','$(i)');

let i = i+1;

loop

So this will give me the last possible value of Prefix, in this case 2.  Is it possible for me to append strings to an existing variable without wiping out the value?

Thanks!

[m_perreault · Accepted solution]

Figured it out

Load
Concat(Chr(39) & Prefix & Chr(39),',') as PrefixList

Resident PrefixTable;

Drop Table Prefix Table;

let vPrefixList = FieldValue('PrefixList',1);