topic Re: Concatenated String as a Variable in App Development

Concatenated String as a Variable

m_perreault — Mon, 05 Nov 2018 22:16:34 GMT

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

Re: Concatenated String as a Variable

m_perreault — Mon, 05 Nov 2018 22:49:38 GMT

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!

Re: Concatenated String as a Variable

m_perreault — Tue, 06 Nov 2018 00:13:56 GMT

Figured it out

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

Resident PrefixTable;

Drop Table Prefix Table;

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