topic Re: Variable in QlikView

Variable

upaliwije — Fri, 06 Nov 2015 06:54:44 GMT

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

Re: Variable

Anonymous — Fri, 06 Nov 2015 06:57:53 GMT

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.

Re: Variable

tamilarasu — Fri, 06 Nov 2015 06:59:48 GMT

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.

Re: Variable

Anonymous — Fri, 06 Nov 2015 07:10:29 GMT

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

Re: Variable

upaliwije — Fri, 06 Nov 2015 07:14:19 GMT

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

Re: Variable

tamilarasu — Fri, 06 Nov 2015 07:19:48 GMT

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.

Re: Variable

Anonymous — Fri, 06 Nov 2015 07:28:58 GMT

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!!

Re: Variable

tamilarasu — Fri, 06 Nov 2015 07:31:50 GMT

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;