If no source files, then do not LOAD

Mon, 26 Jan 2015 13:48:42 GMT

Hi,

I've developed a script for incremental loads that works well, or that is at least what I thought

Just realized it does not work when there is no source file avaliable. An error message occurs when

there is no file and the script tries to save the non existing table into a QVD. Pls see attached.

If no source file I just want to exit the sub.

Any ideas?

Kind Regards,

Olle

FOR EACH vFile IN FileList ('01.Raw_Data\*.txt')
LET vFileNameOnly = Mid('$(vFile)',Index('$(vFile)','\',-1)+1);

Raw_Data_TEXT:
LOAD @1:17,
@18:32,
@33:70,
@71:99,
@111:119,
@100:n,
FileName() as FILENAME
FROM
$(vFile)
(fix, codepage is 1252, embedded labels, header is 3 lines)
WHERE LEFT(@1:17,2)='50' OR LEFT(@71:99,2)='Va';

LEFT JOIN(Raw_Data_TEXT)
//Currency
LOAD MID(@1:17,7,3) as CCY,
@111:119 as [REPORT DATE]

FROM
$(vFile)
(fix, codepage is 1252)
WHERE SubField(@1:n, ' - ', 1) = 'SAS';

STORE * FROM Raw_Data_TEXT INTO 02.Intermediate_Data\$(vFileNameOnly) (txt, delimiter is '\t');
DROP TABLE Raw_Data_TEXT;

Step1_TEXT:
LOAD Trim(RIGHT(AcctNumber,10)) as ACC,
BIC,
[Amount D/C],
KeepChar(Left([Amount D/C],FindOneOf([Amount D/C],',')-1),'1234567890')+ // First part of the number
KeepChar(Mid([Amount D/C],FindOneOf([Amount D/C],',')+1,2),'1234567890')/100 as Amount,
If(Len(AcctNumber) >0,Right([Amount D/C],1)) as [D/C],
If(Len(AcctNumber)=0,Date(Date#(Right([Amount D/C],10), 'DD.MM.YYYY')), Peek(ValueDate)) as ValueDate,
[SAS-Reference],
Date(Date#([REPORT DATE], 'DD.MM.YYYY'))as [REPORT DATE],
CCY,
FILENAME,
IF(RIGHT(filename(),3)='txt','TEXT') as SOURCE,
IF(RIGHT(filename(),3)='txt','YES') as [ILM RESPONSIBLE],
IF(RIGHT(filename(),3)='txt','New transaction') as [TRANSACTION STATUS]

FROM
02.Intermediate_Data\$(vFileNameOnly)
(txt, utf8, embedded labels, delimiter is '\t', msq);

FINAL_TABLE_TEXT:
LOAD RIGHT(ACC,7) as ACC,
BIC as BANKNAME,
IF(TRIM([D/C]) = 'C',Amount*-1,Amount) As AMOUNT,
ValueDate as VD,
[SAS-Reference] as REF,
CCY as CCY,
[REPORT DATE],
SOURCE,
FILENAME,
[ILM RESPONSIBLE],
[TRANSACTION STATUS]

RESIDENT Step1_TEXT
WHERE ACC>1;
DROP TABLE Step1_TEXT;

NEXT vFile;
STORE FINAL_TABLE_TEXT INTO $(vSaveQVD)TEXT.qvd(qvd);

Re: If no source files, then do not LOAD

kuba_michalik — Mon, 26 Jan 2015 14:11:23 GMT

You can test for number of rows in table with NoOfRows function (will return null if table does not exist) and use it as a condition for IF:

IF not IsNull(NoOfRows('FINAL_TABLE_TEXT')) then;

STORE FINAL_TABLE_TEXT INTO $(vSaveQVD)TEXT.qvd(qvd);

ENDIF;

Re: If no source files, then do not LOAD

MarcoWedel — Mon, 26 Jan 2015 14:44:59 GMT

Look up error mode in the help.

Re: If no source files, then do not LOAD

manojkvrajan — Mon, 26 Jan 2015 15:39:39 GMT

I use this script and works for me. Please try this out and check if it meets your requirements. Remember, the Load script should execute only if the Filesize is greater than zero.

FOR EACH vFile IN FileList ('01.Raw_Data\*.txt')

LET vFileNameOnly = Mid('$(vFile)',Index('$(vFile)','\',-1)+1);

IF FileSize('$(vFile)') > 0 THEN

// Put your usual Load Script

ENDIF;

NEXT vFile ;

topic Re: If no source files, then do not LOAD in QlikView

If no source files, then do not LOAD

Re: If no source files, then do not LOAD

Re: If no source files, then do not LOAD

Re: If no source files, then do not LOAD