topic Re: sql select for each day in QlikView

sql select for each day

Tue, 25 Feb 2014 13:01:57 GMT

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

Re: sql select for each day

Anonymous — Tue, 25 Feb 2014 13:14:01 GMT

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

alexandros17 — Tue, 25 Feb 2014 13:17:12 GMT

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

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

Re: sql select for each day

Anonymous — Tue, 25 Feb 2014 13:18:51 GMT

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

Re: sql select for each day

Tue, 25 Feb 2014 13:19:08 GMT

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.

You can use:

LOAD *

where borndate = vDay;

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

from table;

Something like above.

Thanks,

Angad

Re: sql select for each day

Tue, 25 Feb 2014 13:34:44 GMT

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

Re: sql select for each day

Tue, 25 Feb 2014 13:44:37 GMT

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

Re: sql select for each day

Tue, 25 Feb 2014 13:44:57 GMT

Ciao Grazie,

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

Re: sql select for each day

Tue, 25 Feb 2014 13:45:22 GMT

How ?

Re: sql select for each day

Anonymous — Tue, 25 Feb 2014 13:51:59 GMT

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

Re: sql select for each day

alexandros17 — Tue, 25 Feb 2014 13:52:09 GMT

Si ma il formato è DD/MM/YYYY? se è così allora dovrebbe già fiunzionare, altrimenti prova

a girare le date

num(Date#('01/02/2014','DD/MM/YYYY')) in ..... YYYY/MM/DD

Num(Date#(Today(),'DD/MM/YYYY') in YYYY/MM/DD

Dovrebbe andare, fammi sapere

Re: sql select for each day

Tue, 25 Feb 2014 13:53:38 GMT

you can try

where borndate = '$(vYourVariable)'

Re: sql select for each day

Tue, 25 Feb 2014 14:04:14 GMT

Il formato su db è dd/mm/yyyy

l'errore restituito quando lancio lo script è

ORA-00932: tipi di dati incoerenti: previsto DATE, ottenuto NUMBER

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

LET myDateFin = Num(Today());

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

SELECT * from employee_uo where start_date = $(i);

Re: sql select for each day

alexandros17 — Tue, 25 Feb 2014 14:09:32 GMT

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;

Re: sql select for each day

Tue, 25 Feb 2014 17:46:10 GMT

continua a darmi errore, con l'ultima soluzione non trova il campo borndate.

sto impazzendo per un cosa che in teoria dovrebbe essere semplice

Grazie

Simone

Re: Re: sql select for each day

Wed, 26 Feb 2014 09:36:03 GMT

hi Bill

i tried this way :

FOR i = Date#('01/12/2012','DD/MM/YYYY') to Date#('31/12/2012','DD/MM/YYYY')

SELECT cod_uo, employee_id

FROM employee_uo

WHERE $(i) >= start_date AND $(i) <= expiry_date;

NEXT;

Lookup Error ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SELECT cod_uo, employee_id

FROM employee_uo

WHERE 41244 >= start_date AND 41244 <= expiry_date

Any solution?

tyvm

Re: sql select for each day

maxgro — Wed, 26 Feb 2014 09:59:51 GMT

this works on an oracle dbms

hiredate is a date data type in my table

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=xxxx;Data Source=xxxxx;Extended Properties=""] (XPassword is xxxxxxxxxxxxx);

LET myDateIni = Num(Date#('01/01/2014','DD/MM/YYYY'));

LET myDateFin = Num(Today());

FOR i = myDateIni to myDateFin

TRACE $(i);

LET d=date($(i), 'DD/MM/YYYY');

trace $(d);

LOAD *;

SQL SELECT * FROM "TEST"."EMP" where hiredate >= to_date('$(d)', 'DD/MM/YYYY');

Re: Re: sql select for each day

Wed, 26 Feb 2014 10:21:18 GMT

let vint=interval(Date1-Date2,'D')

For i =0 to vint

let Vdate=dayname('Date1',$(i))

SELECT cod_uo, employee_id

FROM employee_uo

WHERE vdate >= start_date AND vdate <= expiry_date;

Re: sql select for each day

Wed, 26 Feb 2014 10:29:21 GMT

Try below...

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

from table

where table.borndate >='$(vDday)' and table.borndate <=Today()

Re: sql select for each day

maxgro — Wed, 26 Feb 2014 10:32:37 GMT

stai confrontando la start_date che sul db oracle è una date con la variabile qlikview che contiene un numero, da cui l'errore oracle, cioè stai eseguendo qualcosa di simile a

SELECT * from employee_uo where start_date = 40000;