TimeStamp format issue

[jelly_gvm]

Hello all,

The below is my script to get lats 35 days data in QVD, but it's picking only current month ( Jan 2016 ) data only not from the last month ( Dec 2015 ).

LOAD Timestamp(Max(CREATION_DATE)),'MM/DD/YYYY hh:mm:ss') as Date_1

From $(vPath)Sales.qvd (qvd);

LET vLastUpdatedate = Peek('Date_1',0);

LOAD * FROM $(vPath)Sales.qvd (qvd) Where Not Exists(Key1)
and Timestamp(CREATION_DATE,'MM/DD/YYYY hh:mm:ss') <= '$(vLastUpdatedate)'
and Timestamp(CREATION_DATE,'MM/DD/YYYY hh:mm:ss') > Timestamp(Today()-35,'MM/DD/YYYY hh:mm:ss');

$(vLastUpdatedate)' = QVD max date

Issue :- I am not able to get the data for past 35 days.

please help me to resolve the issue.

Thanks in advance

Regards,

Ram

[MayilVahanan]

Hi

What is the value in vLastUpdatedate  ?

You can change the Timestamp to Floor(Timestamp()) and verify it

[jelly_gvm]

Hi Sunny,

Thanks for the help.

earlier it was working for me, but suddenly there is an issue to not picking proper data in the QVD.

Can we use instead of TIMESTAMP function to convert to below format.

'MM/DD/YYYY hh:mm:ss'.

Regards,

Ram

[sasikanth]

HI Ram,

That we can`t  say without seeing your date format ,

please share your App or sample data of your QVD ,

it would be more helpful to resolve your issue

[jelly_gvm]

Hi Sunny,

the below is my exact code,  can you plese change if any changes is required.

IF NOT IsNull(QvdCreateTime('$(vPath)INT_DET.qvd')) then
LoadTime2:
LOAD Timestamp(Max(SOA_HEADER_DATE),'MM/DD/YYYY hh:mm:ss') as Date_1 From $(vPath)INT_DET.qvd (qvd);
LET vLastUpdatedate = Peek('Date_1',0);
DROP Tables LoadTime2;
ENDIF

IF NOT IsNull(QvdCreateTime('$(vPath)INT_DET.qvd')) then
INT_DET:
SQL SELECT a.DETAIL_ID,a.SUMMARY_ID, a.LAST_UPDATE_DATE, b.LAST_UPDATE_DATE as SOA_HEADER_DATE, a.MESSAGE_CLASS,a.STAGE,ERROR_CODE,a.ERROR_DESCRIPTION
FROM SOAENT_ETN_ERR_FRMWRK.ETN_EE_INT_MNTR_TXN_DETAIL a, SOAENT_ETN_ERR_FRMWRK.ETN_EE_INT_MNTR_TXN_HEADER b, SOAENT_ETN_ERR_FRMWRK.INTERFACES c
WHERE a.SUMMARY_ID = b.SUMMARY_ID
and b.LAST_UPDATE_DATE <= sysdate
and b.LAST_UPDATE_DATE >= sysdate - 34
and b.interface_name = c.interface_id
and c.project_code ='UNIFY'
and b.LAST_UPDATE_DATE >= To_Date('$(vLastUpdatedate)','MM/DD/YYYY HH24:MI:SS');

Concatenate(INT_DET)

LOAD * FROM
$(vPath)INT_DET.qvd (qvd) Where Not Exists(DETAIL_ID)
and Timestamp(SOA_HEADER_DATE,'MM/DD/YYYY hh:mm:ss[.fff] TT') <= '$(vLastUpdatedate)'

and Timestamp(SOA_HEADER_DATE,'MM/DD/YYYY hh:mm:ss[.fff] TT') >=Timestamp(Today()-35,'MM/DD/YYYY hh:mm:ss[.fff] TT');

STORE INT_DET into $(vPath)INT_DET.qvd (qvd);

ELSE

INT_DET:
SQL SELECT a.DETAIL_ID,a.SUMMARY_ID, a.LAST_UPDATE_DATE, b.LAST_UPDATE_DATE as SOA_HEADER_DATE, a.MESSAGE_CLASS,a.STAGE,ERROR_CODE,a.ERROR_DESCRIPTION
FROM SOAENT_ETN_ERR_FRMWRK.ETN_EE_INT_MNTR_TXN_DETAIL a, SOAENT_ETN_ERR_FRMWRK.ETN_EE_INT_MNTR_TXN_HEADER b, SOAENT_ETN_ERR_FRMWRK.INTERFACES c
WHERE a.SUMMARY_ID = b.SUMMARY_ID
and b.LAST_UPDATE_DATE <= sysdate
and b.LAST_UPDATE_DATE >= sysdate - 34
and b.interface_name = c.interface_id
and c.project_code ='UNIFY';

STORE INT_DET into $(vPath)INT_DET.qvd (qvd);

ENDIF
DROP Table INT_DET;

Thanks,

Ram