Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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.
But will QV consider it in SQL query when you are fetching data from SQL server?
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
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.
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!!
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;