topic Re: load script problem in QlikView

load script problem

Sat, 25 Jun 2011 13:43:59 GMT

Hello,

I have a table where i am fetching weekly data and am inserting it into a qvd file. as below

[SYNCRO MAX WEEK]:
LOAD MAX([SYNCRO Plan Week]) AS [MAX PLAN WEEK]
FROM [SYNCRO PV].QVD;
LET MAX_PLAN_WEEK=PEEK('[MAX PLAN WEEK]',0,'[SYNCRO MAX WEEK]');
[SYNCRO PV]:
NOCONCATENATE
LOAD 
  ITEMDATE,
  ITEMCONFIRMATION AS [Material confirmation],
  If(Len( ITEMCONFIRMATION)>0,'True','False') as [Item Response],
  ITEMDEMAND AS [Material demand],
  ITEM_CODE,
  SUPPLIER_CODE,
  PLANT,
  PLAN_WEEK AS [SYNCRO Plan Week],
  PAI_VALUE AS [Projected available Inventory],
  ITEMINTERVAL AS [Week or Period],
 [FORE_WEEK] as [SYNCRO Forecasted Week]
FROM
[$(PATH_VAR)\SYNCRO\DSA PV P01 Data.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE 
 PLAN_WEEK > $(MAX_PLAN_WEEK);
STORE [SYNCRO PV] INTO [SYNCRO PV].QVD;

Now the problem is that Qlikview is giving error over statement

WHERE

PLAN_WEEK > $(MAX_PLAN_WEEK);

Can someone please help what is wrong with this statement. Also what happens when first time there is no QVD file and this variable is null or empty?

Arif.

Re: load script problem

nagaiank — Sat, 25 Jun 2011 14:17:20 GMT

Replace the following line in your script

LET MAX_PLAN_WEEK=PEEK('[MAX PLAN WEEK]',0,'[SYNCRO MAX WEEK]');

with

LET MAX_PLAN_WEEK=PEEK('MAX PLAN WEEK',0,[SYNCRO MAX WEEK]);

and see if it works.

Re: load script problem

Sat, 25 Jun 2011 15:04:52 GMT

now i have replaced it with the following line

LET MAX_PLAN_WEEK=if(LEN(PEEK('MAX PLAN WEEK',0,'SYNCRO MAX WEEK'))>0,MAX_PLAN_WEEK,'2008P01');

and it is working. now the problem is that when first time, the script executes, there is no qvd, so it gives an error. How can i make this load script so that it checks that if the qvd exists, then read from it, otherwise, just put 2008P01 as a default value in the variable.

Arif

Re: load script problem

Sat, 25 Jun 2011 15:09:50 GMT

actually the code is as below

LET MAX_PLAN_WEEK=if(LEN(PEEK('MAX PLAN WEEK',0,'SYNCRO MAX WEEK'))>0,PEEK('MAX PLAN WEEK',0,'SYNCRO MAX WEEK'),'2008P01');

now i want to load from qvd only if it exists. how do i do that?

Arif

Re: load script problem

kaushiknsolanki — Sat, 25 Jun 2011 17:36:03 GMT

Hi,

Try this,

SET ErrorMode =0;

[SYNCRO MAX WEEK]: LOAD MAX([SYNCRO Plan Week]) AS [MAX PLAN WEEK] FROM [SYNCRO PV].QVD; LET MAX_PLAN_WEEK=IF(isnull(PEEK('MAX PLAN WEEK',0,'SYNCRO MAX WEEK')),'2008P01',PEEK('MAX PLAN WEEK',0,'SYNCRO MAX WEEK'));

Regards,

Kaushik Solanki

Re: load script problem

Mon, 27 Jun 2011 05:10:52 GMT

To check whether the QVD exists or not, you can use the following code:

IF FileSize('$(QVDFILE)') > 0 THEN
    SET QVD_EXISTS=1;        // true
ELSE
    SET QVD_EXISTS=0;        // false
END IF

Source: Incremental Load in Rob's Cookbook.