Where clause with variable

[#1]

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

[rubenmarin]

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

[#3]

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.

[rubenmarin]

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

NEXT

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)

[rubenmarin · Accepted solution]

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

NEXT

[#6]

Thanks a lot!! It works perfectly