topic Re: TimeStamp format issue in QlikView

TimeStamp format issue

jelly_gvm — Tue, 05 Jan 2016 09:14:35 GMT

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

Re: TimeStamp format issue

senpradip007 — Tue, 05 Jan 2016 09:20:43 GMT

Try like:

Load

....

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

Re: TimeStamp format issue

puttemans — Tue, 05 Jan 2016 09:24:38 GMT

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

Re: TimeStamp format issue

sasikanth — Tue, 05 Jan 2016 09:25:16 GMT

hi,

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

Please attach a sheet with sample data

Re: TimeStamp format issue

Tue, 05 Jan 2016 09:35:04 GMT

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

Re: TimeStamp format issue

Tue, 05 Jan 2016 09:36:53 GMT

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;

Re: TimeStamp format issue

jelly_gvm — Tue, 05 Jan 2016 09:52:18 GMT

Hi Pradip,

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

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

Please suggest.

Thanks,

Ram

Re: TimeStamp format issue

jelly_gvm — Tue, 05 Jan 2016 09:57:22 GMT

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

Re: TimeStamp format issue

senpradip007 — Tue, 05 Jan 2016 10:37:38 GMT

Can you share your sample data?

Re: TimeStamp format issue

sasikanth — Tue, 05 Jan 2016 10:39:42 GMT

hi RAM,

Its working for us

PFA

Re: TimeStamp format issue

MayilVahanan — Tue, 05 Jan 2016 10:42:34 GMT

What is the value in vLastUpdatedate ?

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

Re: TimeStamp format issue

jelly_gvm — Tue, 05 Jan 2016 10:43:49 GMT

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

Re: TimeStamp format issue

sasikanth — Tue, 05 Jan 2016 10:59:53 GMT

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

Re: TimeStamp format issue

jelly_gvm — Wed, 06 Jan 2016 10:00:37 GMT

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