topic Script Failure In Apps in QlikView

Script Failure In Apps

sspawar88 — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Script Failure In Apps

avinashelite — Mon, 11 Apr 2016 09:31:22 GMT

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 ??

Re: Script Failure In Apps

Kushal_Chawda — Mon, 11 Apr 2016 09:31:48 GMT

what is the error you are getting?

Re: Script Failure In Apps

sspawar88 — Mon, 11 Apr 2016 09:34:20 GMT

Re: Script Failure In Apps

Kushal_Chawda — Mon, 11 Apr 2016 09:34:28 GMT

or try

VOLUMETRIC2:

LOAD

MAX(DQ_TIME_ID1) AS Min_DQ_Time

RESIDENT VOLUMETRIC where DQ_TIME_ID1<=$(vCurrDateData);

Re: Script Failure In Apps

Kushal_Chawda — Mon, 11 Apr 2016 09:36:17 GMT

I think you have not pasted entire script. Error showing here is next part of the script

Re: Script Failure In Apps

sspawar88 — Mon, 11 Apr 2016 09:37:57 GMT

Thats not correct.

i getting second max from database on basis of DQ_TIME_ID1

Re: Script Failure In Apps

jonathandienst — Mon, 11 Apr 2016 09:39:05 GMT

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);

Re: Script Failure In Apps

sspawar88 — Mon, 11 Apr 2016 09:46:09 GMT

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

Re: Script Failure In Apps

sspawar88 — Mon, 11 Apr 2016 09:48:00 GMT

Can you please check now

Re: Script Failure In Apps

Kushal_Chawda — Mon, 11 Apr 2016 09:48:29 GMT

if you want the second max then try below

VOLUMETRIC:

Max:

LOAD max(DQ_TIME_ID ) as MAX1, // max

max(DQ_TIME_ID,2) as MAX2 // second max

resident VOLUMETRIC;

Re: Script Failure In Apps

Peter_Cammaert — Mon, 11 Apr 2016 09:52:50 GMT

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.

Re: Script Failure In Apps

Kushal_Chawda — Mon, 11 Apr 2016 09:53:38 GMT

try this

VOLUMETRIC:

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;