Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
When I have multiple LOAD_DATE the script willl run but whenever i have single date in Database following script will fail.
Generally the failure is at VOLUMETRIC2 table.
What is the reason?
VOLUMETRIC:
LOAD
DQ_TIME_ID AS DQ_TIME_ID1,
Date(ApplyMap('TIMEDQ',DQ_TIME_ID)) as LOAD_DATE,
ApplyMap('PHYSICALDATASOURCE',PHYSICAL_DATA_SOURCE_ID) as FILE_NAME,
CHECK_POINT_ID,
ROW_CNT
FROM '../Stage/QVDs/Stage1_METRIC_VOLUME.qvd'(qvd);
VOLUMETRIC1:
LOAD
MAX(DQ_TIME_ID1) AS Max_DQ_Time
RESIDENT VOLUMETRIC ;
LET vCurrDateData=peek('Max_DQ_Time',0,'VOLUMETRIC1');
drop table VOLUMETRIC1;
VOLUMETRIC2:
LOAD
MAX(DQ_TIME_ID1) AS Min_DQ_Time
RESIDENT VOLUMETRIC where DQ_TIME_ID1<$(vCurrDateData);
LET vCurrDateData1=peek('Min_DQ_Time',0,'VOLUMETRIC2');
drop table VOLUMETRIC2;
VOLUMETRIC_New:
load
*,
'' as VOLUMETRICjunk
resident VOLUMETRIC where DQ_TIME_ID1=$(vCurrDateData) ;
left join(VOLUMETRIC_New)
VOLMAX:
LOAD
//DQ_TIME_ID1,
FILE_NAME,
ROW_CNT as Min_Row_Cnt,
//DW_LOAD_TIMESTAMP as Min_Date,
LOAD_DATE as Min_LOAD_DATE
Resident VOLUMETRIC where DQ_TIME_ID1 =$(vCurrDateData1);
drop table VOLUMETRIC;
if you want the second max then try below
VOLUMETRIC:
LOAD
DQ_TIME_ID AS DQ_TIME_ID1,
Date(ApplyMap('TIMEDQ',DQ_TIME_ID)) as LOAD_DATE,
ApplyMap('PHYSICALDATASOURCE',PHYSICAL_DATA_SOURCE_ID) as FILE_NAME,
CHECK_POINT_ID,
ROW_CNT
FROM '../Stage/QVDs/Stage1_METRIC_VOLUME.qvd'(qvd);
Max:
LOAD max(DQ_TIME_ID ) as MAX1, // max
max(DQ_TIME_ID,2) as MAX2 // second max
resident VOLUMETRIC;
This statement ends with a $-sign substitution but the variable that comes last in the WHERE clause (e.g. vCurrDateData) either is empty or doesn't exist. This is the entire statement and it ends with an equal sign.
Satish, please check whether the following statement actually does put a value in the variable:
LET vCurrDateData=peek('Max_DQ_Time',0,'VOLUMETRIC1');
Also note that the MAX() function can be used like you do in the second LOAD statement. However, it will return a NULL value if the field specified as a parameter (e.g. DQ_TIME_ID1) doesn't contain at least one numerical value.
try this
VOLUMETRIC:
LOAD
DQ_TIME_ID AS DQ_TIME_ID1,
Date(ApplyMap('TIMEDQ',DQ_TIME_ID)) as LOAD_DATE,
ApplyMap('PHYSICALDATASOURCE',PHYSICAL_DATA_SOURCE_ID) as FILE_NAME,
CHECK_POINT_ID,
ROW_CNT
FROM '../Stage/QVDs/Stage1_METRIC_VOLUME.qvd'(qvd);
MAX:
LOAD max(DQ_TIME_ID1) as MAX1
max(DQ_TIME_ID1,2) as MAX2
Resident VOLUMETRIC;
LET vCurrDateData=peek('MAX1',0,'MAX');
LET vCurrDateData1=peek('MAX2',0,'MAX');
VOLUMETRIC_New:
load
*,
'' as VOLUMETRICjunk
resident VOLUMETRIC where DQ_TIME_ID1=$(vCurrDateData) ;
left join(VOLUMETRIC_New)
VOLMAX:
LOAD
//DQ_TIME_ID1,
FILE_NAME,
ROW_CNT as Min_Row_Cnt,
//DW_LOAD_TIMESTAMP as Min_Date,
LOAD_DATE as Min_LOAD_DATE
Resident VOLUMETRIC where DQ_TIME_ID1 =$(vCurrDateData1);
drop table VOLUMETRIC;