New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Contributor

How to get the data for past 75 days from the current date as the date is in epoch format, can anyo

How to get the data for past 75 days  from the current date as the date is in epoch format, can anyone please help

4 Replies
Contributor

Re: How to get the data for past 75 days from the current date as the date is in epoch format, can

Honored Contributor II

Re: How to get the data for past 75 days from the current date as the date is in epoch format, can

Hi

Please try the below. I am not using Qlik at the moment. I will reply with the full solution unless you can't get it to work

1 days = 86400 secs

NUM(TODAY()) = X
NUM(1ST JAN 1970) = Y

Diffence in date = x-y+1 ( if you include today as well)
multiply (x-y+1)*86400 which is the epoc format date for today
compare the date with above date-86400*75 and do your calculation

regards
Learning never stops.
Contributor

Re: How to get the data for past 75 days from the current date as the date is in epoch format, can

Change:

CHG,

applyMap(chgmap',STATUS,'#NA')  AS  CHG_STATUS,
CHANGE_GRP ,
Timestamp(Timestamp('1970-01-01 00:00:00.000') + CHG_END_DATE/60/60/24) as END_DATE_TIMESTAMP,
Timestamp(Timestamp('1970-01-01 00:00:00.000') + CHG_START_DATE/60/60/24) as START_DATE_TIMESTAMP

FROM
[\$(vGPATH)\change.qvd]
(qvd)  where START_DATE>=1538352000;

For example ,Here i have hardcoded the data. as the start date will start from oct 1st 2018 12.00  am .

I need the data  which will show for the last 75 days in load script itself from the current date.

;

Honored Contributor II

Re: How to get the data for past 75 days from the current date as the date is in epoch format, can

can you try this

Change:

CHG,

applyMap(chgmap',STATUS,'#NA') AS CHG_STATUS,
CHANGE_GRP ,
Timestamp(Timestamp('1970-01-01 00:00:00.000') + CHG_END_DATE/60/60/24) as END_DATE_TIMESTAMP,
Timestamp(Timestamp('1970-01-01 00:00:00.000') + CHG_START_DATE/60/60/24) as START_DATE_TIMESTAMP

FROM
[\$(vGPATH)\change.qvd]
(qvd) where START_DATE>= ( ((num(today())-25569+1)*86400) - (75*86400) );

Learning never stops.