Variable

[upaliwije]

I have following script in my data load

let Vagedate=Date(Makedate(2015,12,31),'D/M/YYYY');

assets:

LOAD *,

(Vagedate-PURCHASE_DATE)/12 AS AGE_M;

sql

select location as BCOD,

           ID_No,Serial_No,Description,Used_By,Catagory,purchase_Date,Purch_Year,Purch_Month,........

When I load the data I get the message  

Field not found - <Vagedate>

I want to calculate the filed highlighted in Blue. What Error is causing the above message. Pls help to rectify this

[Anonymous]

It seems you are using this variable while fetching the data from SQL, which would not work.

First fetch the data from SQL and then use that variable in QV by taking resident , it will work for sure.

[tamilarasu]

Hi Upali,

Let Vagedate=Date(Makedate(2015,12,31),'D/M/YYYY');

assets:

LOAD *,

($(Vagedate)-PURCHASE_DATE)/12 AS AGE_M;

------------------

Since Vagedate is a variable, you need to use $ sign.

Let me know.

[Anonymous]

But will QV consider it in SQL query when you are fetching data from SQL server?

[upaliwije]

Thanks 

It is working but I do not get expected resutls

PURCHASE_DATE	AGE_M
1/12/1988 0:00	-2679.499984
1/21/1988 0:00	-2680.249984
2/9/1988 0:00	-2681.833317
1/5/1989 0:00	-2709.416651
4/1/1989 0:00	-2716.583317

AGE_M   is in correct. Pls help to rectify

[tamilarasu]

Hi Balraj,

Actually Upali is trying to load data from SQL and then doing preceding load. Here, QV is considering the variable Vagedate as field. So we can use $ sign to evaluate the variable.I guess it will work.

[Anonymous]

Make sure format of both the date should be same.

then you can use NUM()

Let Vagedate=num(Date(Makedate(2015,12,31),'D/M/YYYY'));

assets:

LOAD *,

($(Vagedate)-num(date(PURCHASE_DATE,'D/M/YYYY')))/12 AS AGE_M;

hope this will work!!

[tamilarasu · Accepted solution]

Hi Use number function. Use proper date format.

Edit: Something Like,

Let Vagedate=Num(Date(Makedate(2015,12,31),'D/M/YYYY'));

assets:

LOAD *,

($(Vagedate)- (Num(Timestamp#(PURCHASE_DATE,'M/D/YYYY hh:mm'))))/12 AS AGE_M;