Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Transform Date to Number while keeping the value

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

1 Solution

Accepted Solutions

Re: Transform Date to Number while keeping the value

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)';

8 Replies

Re: Transform Date to Number while keeping the value

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

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

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

anbu1984
Honored Contributor III

Re: Transform Date to Number while keeping the value

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

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

Not applicable

Re: Transform Date to Number while keeping the value

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?

anbu1984
Honored Contributor III

Re: Transform Date to Number while keeping the value

Can you post sample qvw

Re: Transform Date to Number while keeping the value

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

or

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

?

Re: Transform Date to Number while keeping the value

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)';

Not applicable

Re: Transform Date to Number while keeping the value

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

Thanks guys!!