topic Re: Where clause with variable in QlikView

Where clause with variable

Wed, 01 Oct 2014 07:34:21 GMT

Hi,

When loading my data's, I'm restricting these using a where clause that looks like this:

where OPE_DATE >= '01/01/2014'

so it only selects data's starting on January 1st.

This script was used for some test and now that everything works, I would like to restrict the load on a variable.

In fact the data I'm loading, is coming from 5 printers (offset) and since the datas are not correct previous to a specific date, we defined that date for each printer in a excel file:

Printer Date_start

A 01/02/2014

B 16/02/2014

C ...

D ...

E ...

I'm loading the excel file first and i tried to store it in a variable with:

LET vDateStart = Date_Start (the field where the information is written) and when loading my datas, I would like the where clause to look like this:

where OPE_DATE >= $(vDateStart)

but unfortenutalety it is not working 😕 actually, the variable is not set because when I try to show the information of the variable in a textbox, nothing appears

Re: Where clause with variable

rubenmarin — Wed, 01 Oct 2014 07:42:05 GMT

Hi Roger, maybe the issue is when you assign the field to the variable, you can't assign a field, you need to assign a value of that field using functions like FieldValue or Peek, ie:

LET vDateStart = FieldValue(Date_Start, 1);

Also, in the where clause you probably need to simple quote the variable, so 01/02/2014 don't pass as 01 divided by 02 divided by 2014, ie:

OPE_DATE >= '$(vDateStart)'

Re: Where clause with variable

Wed, 01 Oct 2014 07:59:33 GMT

Thanks this should work

Do you know if there is a way to automate this, because in a few weeks, we will add some machine to the list with other dates.

Re: Where clause with variable

rubenmarin — Wed, 01 Oct 2014 08:12:39 GMT

Not sure to understand, as a guess/tip, you can build a bucle, ie:

Excel:

LOAD Date_Start

FROM ...xls (...);

FOR i=1 to fieldValueCount('Date_Start')

LET vDateStart = FieldValue(Date_Start, $(i));

Table:

LOAD ...

where OPE_DATE >= '$(vDateStart)'

Problably don't meet your requirements, it will load all data in 'Table' table, and two differents dates will create duplicate records, ie if first date is 01/02/2014 and 2nd date is 16/02/2014, both iteraton would load records where OPE_DATE >=16/02/2014 (as they are also >= 01/02/2014 and were loaded in first iteration)

Re: Where clause with variable

rubenmarin — Wed, 01 Oct 2014 08:20:57 GMT

If PRINTER is another field you can filter then you can add it to the where clause:

Excel:

LOAD Printer,

Date_Start

FROM ...xls (...);

FOR i=0 to NoOfRows('Excel')-1

LET vPrinter= Peek('Printer', $(i), 'Excel');

LET vDateStart=Peek('Date_Start', $(i), 'Excel');

Table:

LOAD ...

where OPE_DATE >= '$(vDateStart)' and PRINTER='$(vPrinter)'

Re: Where clause with variable

Wed, 01 Oct 2014 08:52:18 GMT

Thanks a lot!! It works perfectly