Where clause problem

[caio_caminoski]

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!

[caio_caminoski]

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

[jaimeaguilar · Accepted solution]

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

[caio_caminoski]

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;