Solved: sql select for each day - Qlik Community

Report Inappropriate Content · ‎2014-02-25

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

alexandros17 · ‎2014-02-25

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;

Anonymous · ‎2014-02-25

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

alexandros17 · ‎2014-02-25

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

Anonymous · ‎2014-02-25

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

Report Inappropriate Content · ‎2014-02-25

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

Report Inappropriate Content · ‎2014-02-25

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

Report Inappropriate Content · ‎2014-02-25

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

Report Inappropriate Content · ‎2014-02-25

Ciao Grazie,

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

Report Inappropriate Content · ‎2014-02-25

How ?

Anonymous · ‎2014-02-25

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