STORE QVD with only part of ODBC connection name

[johngouws]

Good morning.

I am looking for a solution where I can store a QVD in the name of the company, excluding the fin year portion of the ODBC connection name.

The below script works, but vCompany includes the fin year 23. For example, in the case of ODBC ABC23 I need to store the QVD as ABC_DailyTransactions.qvd.

NOTE: ABC and DEF are company names and 23 is the Fin Year.

For Each vCompany in 'ABC23','DEF23'

ODBC CONNECT32 TO $(vCompany);

t1:
SQL SELECT
*
FROM DailyTransactions;

STORE t1 INTO [$(vCompany)_DailyTransactions.qvd](qvd);
DROP TABLE t1;

Next vCompany

Your help is appreciated - thank you. 

[jmartineze · Accepted solution]

Hello,

if i understand, you can try this

let vComp=left('$(vCompany)',3);

STORE t1 INTO [$(vComp)_DailyTransactions.qvd](qvd);

[johngouws]

Hi @jmartineze . 

So simple. Thank you. I added an additional step and because the company name changes in terms of length, I changed the LET a bit. It now reads: 

For Each vCompany in 'ABC23','DEF23'

ODBC CONNECT32 TO $(vCompany);

t1:
SQL SELECT
*
FROM DailyTransactions;

LET vComp = Left('$(vCompany)', Len(Trim('$(vCompany)')) - 2);

STORE t1 INTO [$(vComp)_DailyTransactions.qvd](qvd);
DROP TABLE t1;

Next vCompany

Thank you very much.