Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

extract data from a range of date

Hi

i use 2 variables.

1)vFrom (eg. 11/11/2016)

and

2) vTo (example 13/11/2016).

Example:

From (11/November/2016)

vTo (13/November/2016)

OrderDate: I need to extract all data that fall between the range of date (11/november, 12/november, 13/novembre)

My idea is to use a similar formula:

only(If([OrderDate] >= vFrom and <= vTo, [OrderDate], 'error')

but it doesn't work.

Could you suggest a "method"?

Thanks

10 Replies

Re: extract data from a range of date

You want to extract the data from table or you want to just display the data in this range

Ex:-

Let vFrom  = '11/11/2016';

Let vTo = '13/11/2016';

LOAD * From table

Where  [OrderDate] >= '$(vFrom)' and [OrderDate] <= '$(vTo)';

Regards

Anand

Partner
Partner

Re: extract data from a range of date

extract data.

If I understand correctly, I need to work side script (sql) and not front end (user interface)

Re: extract data from a range of date

Yes it depends on the requirement you can do this on the front end also.

Regards

Anand

Re: extract data from a range of date

On the front end it can be as simple as applying selection, on back end it will reduce data set available for further analysis.

Consider both scenarios.

Also keep in mind that different formatting of dates can be done on DB so when createing and calling variables keep that in mind.

SQL should work with YYYY-MM-DD but Oracle DB may need date in this format DD-MMM-YY

regards

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
MVP & Luminary
MVP & Luminary

Re: extract data from a range of date

Hi,

Try like this

LOAD * From table

Where  [OrderDate] >= '$(vFrom)' and [OrderDate] <= '$(vTo)';

If you are using SQL then try

SELECT

*

FROM TableName

[OrderDate] >= '$(vFrom)' and [OrderDate] <= '$(vTo)';


Your date format in variables should be same as in the database.


Regards,

Jagan.

Highlighted
Partner
Partner

Re: extract data from a range of date

Hi,

SQL SELECT

OrderNumber,

OrderDate,

FROM S1230213a.prova.Ordini

where OrderDate >= '$(vFrom)' and OrderDate <= '$(vTo)';

but when I load my script ..

Si è verificato il seguente errore:

SQL##f - SqlState: S1000, ErrorCode: 4294966994, ErrorMsg: [IBM][Programma di controllo ODBC di System i Access][DB2 per i5/OS]SQL0302 - Errore di conversione sulla variabile host o sul parametro *N.

L'errore si è verificato qui:

SQL SELECT
    DTRIRC,
   
FROM S1230213a.prova.Ordini

where DTRIRC >= '03/11/2016' and DTRIRC <= '08/11/2016'

What do it mean by "host variable" or "parameter * N"? .

The good thing is that the value of variables vFrom (03/11/2016) and vTo (08/11/2016) is the last loaded values on the user side (user interface project)

rgvavihs
Valued Contributor

Re: extract data from a range of date

Hi

sql select * from

where orderdate >= '$(VFrom)' and orderdate <= '$(vTo)';

Partner
Partner

Re: extract data from a range of date

Hi,

same signal

Partner
Partner

Re: extract data from a range of date

hi,

ops,

qlik date is gg/mm/yyyy however "sql date" is aaaammdd

Mumble Mumble, I need to convert date from gg/mm/yyyy to aaaammdd