Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

sql select for each day

Hi all,

i have a select like this :

sql select table.name, table.surname, table.age

from table

where table.borndate = vDday

I need vDay loops between 2 values(i.e. 20120101 and sysdate)

Result set will be a list of record for each day

tyvm

Simone

1 Solution

Accepted Solutions

Re: sql select for each day

Ciao Simone,

La mia query gira in MSSQL, la tua in oracle, quando ci sono problemi di formato dovuti al DB, fai lavorare qlik, prova a spostare la logica nel load in questa maniera e fammi sapere

FOR i = num(Date#('01/02/2014','DD/MM/YYYY')) to Num(Date#(Today(),'DD/MM/YYYY))

      LOAD * where Num(borndate) = $(i);

     SELECT * from myTable;

NEXT

19 Replies

Re: sql select for each day

Simone

I may be mis-understanding, but how about you extract all your data into QlikView, with say :

Data :

sql select

table.name,

table.surname,

table.age ,

table.borndate

from table

;

From that you may well be able to create a dashboard objects as per your needs.

I am unclear as what end result you are hoping to achieve though.  Could you clarify ?

Best Regards,     Bill

Re: sql select for each day

LET myDateIni = num(Date#('01/02/2013','DD/MM/YYYY'));

LET myDateFin = Num(Today());

FOR i = num(Date#('01/02/2014','DD/MM/YYYY')) to Num(Today())

  SELECT * from myTable where borndate = $(i);

NEXT

Devi fare attenzione al formato data del camoi borndate visto che la variabile $(i) è settata in italiano

neetha_p
Honored Contributor

Re: sql select for each day

I think query should be like below,include Greater than sign

sql select table.name, table.surname, table.age

from table

where table.borndate >= vDday

Not applicable

Re: sql select for each day

Qlikview Variable cannot be used directly into the SQL query.

You can either load the whole data and restrict data at UI level, by creating calendar.

Or

You can use:

LOAD *

where borndate = vDay;

SQL select table.name, table.surname, table.age

from table;

Something like above.

Thanks,

Angad

Not applicable

Re: sql select for each day

Hi Angad, we can pass variable values into SQL statement.

Not applicable

Re: sql select for each day

Sorry my bad.....Yes we can pass the variable to SQL. Thanks for pointing this out.

Not applicable

Re: sql select for each day

Ciao Grazie,

la funzione Date mi restituisce un dual, nella tabella ho un campo data.

Not applicable

Re: sql select for each day

How ?

Re: sql select for each day

Simone

To pass the variable to SQL you need to use dollar expansion, as in :

    $(vYourVariable)

This evaluates what is inside the brackets and substitutes this as text.  In this case with just a variable inside, it is simply substituted with the text value of the variable.

Best Regards,     Bill

Community Browser