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;
I think you might not have data i,e date "DQ_TIME_ID1" less than the $(vCurrDateData) so that table won't load at all ...in this case you if you refer that table it will trough an error
what s the exact error your getting ??
what is the error you are getting?
or try
VOLUMETRIC2:
LOAD
MAX(DQ_TIME_ID1) AS Min_DQ_Time
RESIDENT VOLUMETRIC where DQ_TIME_ID1<=$(vCurrDateData);
I think you have not pasted entire script. Error showing here is next part of the script
Thats not correct.
i getting second max from database on basis of DQ_TIME_ID1
There's no obvious reason that the script will fail as long as the first query returns at least one row. However, the variable vCurrDateData1 will contain null if the where clause condition is not satisfied. If there is one record, then the condition will never be satisfied. I think you need to change the condition to:
VOLUMETRIC2:
LOAD
MAX(DQ_TIME_ID1) AS Min_DQ_Time
RESIDENT VOLUMETRIC where DQ_TIME_ID1 <= $(vCurrDateData);
But i'm getting Second Max from this condition which is required
MAX(DQ_TIME_ID1) AS Min_DQ_Time
RESIDENT VOLUMETRIC where DQ_TIME_ID1<$(vCurrDateData);
but at least script has to run by this
Can you please check now