Last 24 hours when timestamp is greater than or equal to reload time

[Aspiring_Developer]

Hi All,

I have one doubt, though it is implemented, not sure if it correct or not.

Below is the sample:-

-Created  a variable reload time:- LET vReloadTime = num(ConvertToLocalTime(Now(), 'UTC-06:00'),'#.#');

-Loading Data

[Cosmino]:
LOAD
PartUID &'-'& MaterialID as 'Process-index',
PartUID as COS_PartUID,
ProcessDesc as COS_ProcessDesc,
MaterialID as COS_MaterialID,
MaterialDesc as COS_MaterialDesc,
Area as COS_Area,
StationID as COS_StationID,
StationDesc as COS_StationDesc,
num(ProdDate) as COS_s_ProdDate,
Date(ProdDate,'MM/DD/YYYY') as COS_d_ProdDate,
ShiftNo,
TStamp,
floor(TStamp) as COS_s_DateTime,
Date(TStamp,'DD/MM/YYYY') as Date,
StatusID as COS_StatusID,
DefectID as COS_DefectID,
DefectDesc as COS_DefectDesc,
DefectLoc as COS_DefectLoc,
ORD(Trim(left(Trim(mid([DefectLoc], 1 + 1)), 1))) - 65 + 0.5 AS [Defect_X],
if(TStamp>=$(vReloadTime)-1,'Last24h','other') as Last24h

FROM
\\SDCTWt0228\Sources\POAE\QVDs\Shopfloor\GREER\Reporting\COS_Process_master.qvd
(qvd)
where TStamp > now()-180;

Problem:

Format of Reload time varible and TStamp:-

Now when we apply the if condition i.e

if(TStamp>=$(vReloadTime)-1,'Last24h','other') as Last24h

The format of TStamp and reload time should be same in order to match Left hand side date with right hand side data. i am not sure with this thing.

Please suggest.

Thanks in advance

[Ksrinivasan]

Hi,

In Dimension you have to set Date and time format for variable, as below for your case

=Date(vReloadTime,'MM/DD/YYYY hh:mm:tt'),

i have tested its working fine

[Kushal_Chawda]

Assuming your TStamp field is in proper  timestamp format (to check this, put max(TStamp) in text object, if it returns value means it is in proper format). Then you can try below. You should have same format while comparing two timestamp.

LET vReloadTime = frac(ConvertToLocalTime(Now(), 'UTC-06:00'));

if(frac(TStamp)>=$(vReloadTime)-1,'Last24h','other') as Last24h