topic Re: Transform Date to Number while keeping the value in QlikView

Transform Date to Number while keeping the value

Tue, 28 Oct 2014 16:06:42 GMT

Hey community

I am having the following issue:

I would like to reload data from a table within SAP. Since I only need the latest 3 months I applied this formula:

date(AddMonths(MonthStart(today()),-2),'YYYYMMDD') which would result by today in 20140801.

The problem is, if I put that into the where statement, it wouldn't work. If i put the plain numbers - hard coded - into it, it works. I think the solution would be, to transform the date format shown above, into a number. Unfortunatley, if I do that, it turns to 41852.

How would you guys go about such an issue?

regards

Sebastian

Re: Transform Date to Number while keeping the value

alexandros17 — Tue, 28 Oct 2014 16:17:58 GMT

All dates have a corresponding numerical value as you experienced, if you need the date again simply do

Date(41852

...

let me know

Re: Transform Date to Number while keeping the value

rubenmarin — Tue, 28 Oct 2014 16:20:25 GMT

Hi Sebastian, if the hard-coded number works you can try with the $-expansion formula:

$(=date(AddMonths(MonthStart(today()),-2),'YYYYMMDD'))

Re: Transform Date to Number while keeping the value

anbu1984 — Tue, 28 Oct 2014 16:26:02 GMT

Let vDt= date(AddMonths(MonthStart(today()),-2),'YYYYMMDD');

Sql Select * from Table where DateField > '$vDt';

Re: Transform Date to Number while keeping the value

Wed, 29 Oct 2014 11:09:38 GMT

Hey all,

I tried both, Ruben's and Anbu's proposal, unfortunatley it didn't work. Especially Anbu's solution seemed promising, since the LET statement should display the content of a variable as it is?

Re: Transform Date to Number while keeping the value

anbu1984 — Wed, 29 Oct 2014 11:12:13 GMT

Can you post sample qvw

Re: Transform Date to Number while keeping the value

MarcoWedel — Wed, 29 Oct 2014 11:27:03 GMT

Text(date(AddMonths(MonthStart(today()),-2),'YYYYMMDD'))

Num#(Text(date(AddMonths(MonthStart(today()),-2),'YYYYMMDD')))

Re: Transform Date to Number while keeping the value

rubenmarin — Wed, 29 Oct 2014 13:59:03 GMT

Following anbu's solution, if vDt has the value you want, maybe it work using () in where clause:

Let vDt= date(AddMonths(MonthStart(today()),-2),'YYYYMMDD');

Sql Select * from Table where DateField > '$(vDt)';

Re: Transform Date to Number while keeping the value

Tue, 04 Nov 2014 09:20:41 GMT

This one worked. The brackets were missing. I think though, that Marco's solution might work too. Going to try that...

Thanks guys!!