Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)'
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.
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)
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
Thanks a lot!! It works perfectly