Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
People,
I am working around a "garbage after statement" error with following code:
//Concatenar com a o QVD já criado
IF not IsNull(QvdCreateTime('$(vDiretorio)QVDs\$(vFactTable).qvd')) then
Concatenate
LOAD *
FROM $(vDiretorio)QVDs\$(vFactTable).qvd (qvd)
Where Timestamp(Timestamp#([DO DateTime])) < $(varMinDateTime)
End IF
Variable varMinDateTime is defined as (obviously it is defined before load):
Temp:
Load
min([DO DateTime]) as minDate,
max([DO DateTime]) as maxDate
Resident $(vFactTable);
Let varMinDateTime = Timestamp(Peek('minDate', 0, 'Temp'));
Let varMaxDateTime = Timestamp(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
Error appear as follows:
Concatenate
LOAD *
FROM QVDs\Deliveries.qvd (qvd)
Where Timestamp(Timestamp#([DO DateTime])) < 02/02/2015 07:03:43
My guess is that error is in Where clause, because when I exclude it, script goes well.
What am I doing wrong? I just want to concatenate fields with creation date [DO DateTime] before variable value. I am not using SQL, but Qlikview syntax.
Field [DO DateTime] is timestamp DD/MM/YYYY hh:mm:ss (tag $numeric, $timestamp in table viewer)
Hope anybody can help me.
Thank you. !
Try this:
Where Timestamp(Timestamp#([DO DateTime],'DD/MM/YYYY hh:mm:ss')) < '$(varMinDateTime)'
Or this:
Where Timestamp(Timestamp#([DO DateTime],'DD/MM/YYYY hh:mm:ss')) < $(varMinDateTime)
Try this:
Where Timestamp(Timestamp#([DO DateTime],'DD/MM/YYYY hh:mm:ss')) < '$(varMinDateTime)'
Or this:
Where Timestamp(Timestamp#([DO DateTime],'DD/MM/YYYY hh:mm:ss')) < $(varMinDateTime)
Temp:
Load
min([DO DateTime]) as minDate,
max([DO DateTime]) as maxDate
Resident $(vFactTable);
Let varMinDateTime =Peek('minDate', 0, 'Temp'); // number
Let varMaxDateTime =Peek('maxDate', 0, 'Temp');
DROP Table Temp;
trace varMinDateTime=$(varMinDateTime);
IF not IsNull(QvdCreateTime('$(vDiretorio)QVDs\$(vFactTable).qvd')) then
Concatenate
LOAD *
FROM $(vDiretorio)QVDs\$(vFactTable).qvd (qvd)
Where Timestamp#([DO DateTime]) < $(varMinDateTime) ; // compare number
End IF ;
Try converting the timestamp in Num at the where clause.
FROM $(vDiretorio)QVDs\$(vFactTable).qvd (qvd)
Where Num(Timestamp(Timestamp#([DO DateTime]))) < Num($(varMinDateTime))
Still getting
Garbage after statement
Concatenate
LOAD *
FROM QVDs\Deliveries.qvd (qvd)
Where Timestamp#([DO DateTime]) < 42037,294247685
Trace retuned varMinDateTime=42037,294247685
But probably the error is due to my system "comma as decimal" as default. We are getting closer.
Thanks Nicole.
The solution was quotes for variable as you mentioned '$(varMinDateTime)'
Because my system default has comma as decimal, script was wrongly considering day fraction as a comma inside the code.
It would be awesome if all countries could have the same units default.
and what about this?
Temp:
Load
min([DO DateTime]) as minDate,
max([DO DateTime]) as maxDate
Resident $(vFactTable);
SET ThousandSep=',';
SET DecimalSep='.';
Let varMinDateTime =Peek('minDate', 0, 'Temp'); // number
Let varMaxDateTime =Peek('maxDate', 0, 'Temp');
DROP Table Temp;
trace varMinDateTime=$(varMinDateTime);
IF not IsNull(QvdCreateTime('$(vDiretorio)QVDs\$(vFactTable).qvd')) then
Concatenate
LOAD *
FROM $(vDiretorio)QVDs\$(vFactTable).qvd (qvd)
Where Timestamp#([DO DateTime]) < $(varMinDateTime) ; // compare number
End IF ;
// ***** back to standard
SET ThousandSep='.';
SET DecimalSep=',';