varible into a field in script load

[ilanbaruch]

hi,

created a variable where the acript start and i want to create a field that will hold var. value is it possible??

[swuehl]

Yes, like

LOAD

     '$(vVar)' as Variable,

     ....

[ilanbaruch]

thank you swuehl,

at the end of this load i want to store it to a qvd with a variable suffix..

tried it like:

Temp:

LOAD

'$(Variable_Name)' as Variable

AutoGenerate(1);

STORE Temp into $(vQVDLoadPath)Temp_$(vToday).qvd (qvd);

DROP Table Temp;

script fails:(

any ideas?

[sunny_talwar · Accepted solution]

May be you need to format vToday variable little differently. What is the format of vToday right now?

I would try suggesting something like this:

LET vToday = Date(Today(), 'DD-MMM-YYYY');

Temp:

LOAD

'$(Variable_Name)' as Variable

AutoGenerate(1);

STORE Temp into $(vQVDLoadPath)Temp_$(vToday).qvd (qvd);

DROP Table Temp;

[swuehl]

Check the expanded variables in debug mode.

You can also enclose the file name and path in []:

STORE Temp into [$(vQVDLoadPath)Temp_$(vToday).qvd] (qvd);

[alex_millan]

Hi ilan,

if that may help, this script works properly, perhaps there's a problem when you declare your variables

Example:

     SET vVar1 = 'Test';

     LET vToday = Date(Today(),'YYYYMMDD');

     SET vQVDLoadPath = 'C:\Users\aaaaaaa\Desktop\Temp_Test\';

     Temp:

     LOAD '$(vVar1)' as Variable

     AutoGenerate(1);

     STORE Temp into $(vQVDLoadPath)Temp_$(vToday).qvd (qvd);

     DROP Table Temp;

HTH

Regards

[jonas_rezende]

Hi, ilan baruch.

See if the variable that contains the path is with backslash (\) at the end. Otherwise, add or explicit in into command.

Ex.:

Alternative 1:

SET vQVDLoadPath = '..\<foldername>\';

Alternative 2:

STORE Temp into $(vQVDLoadPath)\Temp_$(vToday).qvd(qvd);

Drop table Temp;

Hope this helps!

[ilanbaruch]

anoter Q,

LET vToday = DATE(Date#(ReloadTime()),'YYYYMMDD HH:MM:SS');

any idea why this format dont work?

[sunny_talwar]

My guess is because of colon signs in there (:)

Maybe try this if you need timestamp

LET vToday = TimeStamp(ReloadTime(),'YYYYMMDD_hhmmss'); Fixed based the format based on Stefan's comment below

[swuehl]

try without the interpretation:

LET vToday = DATE(ReloadTime(),'YYYYMMDD hh:mm:ss);

Note that minutes are format coded by 'mm'.