topic Re: Where clause problem in QlikView

Where clause problem

caio_caminoski — Thu, 09 Oct 2014 21:59:24 GMT

Hi guys!

I came across some query problem. I am using a date variable to reduce my database in the script. Unfortunatly it doesn't seems to be working. In fact no error shows up, but it still loading older data to my application.

let vDataCorte = '01/05/2014';

Horas:

LOAD `cc_numero` as IdContaCorrente,

`centro_custo` as IdNúcleo,

date(`data_lancamento`,'DD/MM/YY') as Data,

nota as [Descrição Atividades],

quantidade as Horas,

responsavel as IdResponsável;

SQL SELECT `cc_numero`,

`centro_custo`,

`data_lancamento`,

nota,

quantidade,

responsavel

FROM cpj.`tab_coletor_lan`

where `data_lancamento` >= $(vDataCorte);

I am quite sure it is a formating problem. Altough If i try formating my fild `data_lancamento` in the where clause, I end up getting some error.

I would apreciate any help!

Regard!

Re: Where clause problem

caio_caminoski — Thu, 09 Oct 2014 22:07:43 GMT

I forgot to say... `data_lancamento` field format is 'DD/MM/YYYY hh:mm:ss[.fff]'

Re: Where clause problem

jaimeaguilar — Thu, 09 Oct 2014 22:18:16 GMT

hi,

first if you want to take advantage of QlikView functions you have to use the where clause in the load part:

Horas:

LOAD `cc_numero` as IdContaCorrente,

`centro_custo` as IdNúcleo,

date(`data_lancamento`,'DD/MM/YY') as Data,

nota as [Descrição Atividades],

quantidade as Horas,

responsavel as IdResponsável

Where date(floor(`data_lancamento`), 'DD/MM/YYYY') >= $(vDataCorte)

;

SQL SELECT `cc_numero`,

`centro_custo`,

`data_lancamento`,

nota,

quantidade,

responsavel

FROM cpj.`tab_coletor_lan;

otherwise you'll need to use similar SQL Functions.

Also, as an additional tip, as a best practice you may want to do this data load in 2 different qvws or at least in 2 steps. First extract your SQL Data to QVD and then read the QVD and make any filter/transformation you need (like the where clause of your example),

regards

Re: Where clause problem

caio_caminoski — Thu, 09 Oct 2014 22:35:06 GMT

Thank you, Jaime! Good tips!

I've tried your suggestion and the only change i had to made was using the single quotes between the variable.

So this is what i ended up with:

Horas:

LOAD `cc_numero` as IdContaCorrente,

`centro_custo` as IdNúcleo,

date(`data_lancamento`,'DD/MM/YY') as Data,

nota as [Descrição Atividades],

quantidade as Horas,

responsavel as IdResponsável

Where date(floor(`data_lancamento`), 'DD/MM/YYYY') >= '$(vDataCorte)';

SQL SELECT `cc_numero`,

`centro_custo`,

`data_lancamento`,

nota,

quantidade,

responsavel

FROM cpj.`tab_coletor_lan;