Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
Following code & Logic will not recognise the Max(DQ_TIME_ID) & Second Max(DQ_TIME_ID) for CHECK_POINT_ID 2 & 3
Why if Else condition will work for different DQ_TIME_ID & not for same one for check point id's?
What mistake i did in my code?
DQ_TIME_ID | CHECK_POINT_ID | ROW_CNT | PHYSICAL_DATA_SOURCE_ID |
214 | 2 | 205255 | 3 |
214 | 2 | 58420 | 4 |
214 | 2 | 35585 | 5 |
274 | 2 | 25855 | 6 |
274 | 2 | 85235 | 7 |
274 | 2 | 25522 | 8 |
274 | 3 | 24535 | 3 |
274 | 3 | 622255 | 4 |
274 | 3 | 52222 | 5 |
304 | 3 | 82252 | 6 |
304 | 3 | 24733 | 7 |
304 | 3 | 62525 | 8 |
VOLUMETRIC:
LOAD
DQ_TIME_ID AS DQ_TIME_ID1,
ApplyMap('TIMEDQ',DQ_TIME_ID) AS LOAD_DATE,
ApplyMap('PHYSICALDATASOURCE',PHYSICAL_DATA_SOURCE_ID) AS FILE_NAME,
PHYSICAL_DATA_SOURCE_ID,
CHECK_POINT_ID,
ROW_CNT
FROM
(ooxml, embedded labels, table is VOLUMETRIC);
DateCount:
load
count(DISTINCT DQ_TIME_ID1)as IDCount
Resident VOLUMETRIC;
let Datecount=peek('IDCount',0,'DateCount');
DROP Table DateCount;
if $(Datecount)>1 then
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 junk
Resident VOLUMETRIC
WHERE DQ_TIME_ID1=$(vCurrDateData);
Left Join(VOLUMETRIC_New)
VOLMAX:
LOAD
FILE_NAME,
LOAD_DATE as Min_LOAD_Date ,
ROW_CNT AS Min_Row_Cnt
Resident VOLUMETRIC WHERE DQ_TIME_ID1=$(vCurrDateData1);
ELSE
VOLUMETRIC1:
LOAD
max(DQ_TIME_ID1) as Max_DQ_Time
Resident VOLUMETRIC;
let vCurrDateData=peek('Max_DQ_Time',0,'VOLUMETRIC1');
DROP Table VOLUMETRIC1;
VOLUMETRIC_New:
LOAD
*,
'' as junk
Resident VOLUMETRIC
WHERE DQ_TIME_ID1=$(vCurrDateData);
ENDIF
DROP Table VOLUMETRIC;
I am not entirely sure I understand your question. Would you be able to elaborate of what is not working?
Hi Sunny,
I'm having One straight table in which dimensions are LOAD_DATE(This is max load date as in code) ,ROW_CNT(This is max row count as in code) , Min_LOAD_Date , Min_Row_Cnt
and expression is sum(ROW_CNT) when i click on check point id 2 the data in staright table should be for 274(max load date) and 214 (Min_LOAD_Date ) and when i click on check point id 3 the data in staright table should be for 304(max load date) and 274 (Min_LOAD_Date ) but its not working.
Can you please look at this?