extract data from a range of date

[fmazzarelli]

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

[its_anandrjs]

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

[fmazzarelli]

extract data.

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

[its_anandrjs]

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

Regards

Anand

[Lech_Miszkiewicz]

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

[jagan]

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.

[fmazzarelli]

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)

[Anonymous]

Hi

sql select * from

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

[fmazzarelli]

Hi,

same signal

[fmazzarelli]

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