Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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
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
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.