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

[senpradip007]

Try like:

Load

....

<>

Where Num(Creation_Date) > Num(Today()-35);

[puttemans]

Hi Ram,

The Timestamp function will not work to recode your variable into a date. You may use the floor function, or have a look at the attached.

Kind regards,

Johan

[sasikanth]

hi,

did you check the data in that QVD is Upto date ? i.e up to today ,

Please attach a sheet with sample data

[#5]

Works perfect in my test app once you take out the extra braket:

Change this:

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

to this:

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

[#6]

Heres all my test code:

Table:

load * Inline [

CREATION_DATE, Quantity, QtdStock

05/12/2015,  180,      200,

18/02/2015,  80,       200,

10/12/2015,   150,      200,

16/02/2015,  100,      200,

];

tEMP:

LOAD

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

Resident Table;

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

NoConcatenate

Table2:

LOAD

  *

Resident Table

Where

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

  ;

DROP TABLE Table;

[jelly_gvm]

Hi Pradip,

we can't change the below fromat in all the fields

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

Please suggest.

Thanks,

Ram

[jelly_gvm]

Thanks for all,

my issue with the TimeStamp, we can't change the format.

can you please suggest. I have tried with the all the options, but no use.

Thanks,

Ram

[senpradip007]

Can you share your sample data?

[sasikanth]

hi RAM,

Its working for us

PFA