Check files on the directory and load total/partial

Wed, 25 Nov 2020 16:16:04 GMT

Hello Guys.

I am new to development with QlikView and need to develop a solution to check files in a directory if it exists analyzing the log and if your upgrade date is correct.

If the file exists in the log perform will be done part by entering only updating this file in the table.(Partial Update)

If the file exists in the log compares the log aquivo with the directory of the file by checking the date of update, if different perform the recharging of all directory files again (full update).

Registered Log	Last Update Ok	Action
Yes	No	Total Update
Yes	Yes	-
No	No	Partial Update

for this case developed this code:

SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';
SET DayNames='seg;ter;qua;qui;sex;sáb;dom';
SET LongMonthNames='janeiro;fevereiro;março;abril;maio;junho;julho;agosto;setembro;outubro;novembro;dezembro';
SET LongDayNames='segunda-feira;terça-feira;quarta-feira;quinta-feira;sexta-feira;sábado;domingo';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='pt-PT';

//Load Transactions
Transactions:
LOAD CodVenda,
CoFar,
CodiPro,
DataTransacao,
Quantidade,
Total
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//Load Log
Log:
LOAD
lDirectory,
lLastUpdate
FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

//Verify directory files
sub DoDir (Root)
for each Ext in 'xlsx'
for each File in filelist (Root&'\*.' &Ext)

//Load data current files
       TempFiles:
LOAD '$(File)'as fDirectory, FileTime( '$(File)' )as fLastUpdate autogenerate 1;

Let vfLastUpdate        = Peek('fLastUpdate',0,'TempFiles');
Let vfDirectory            = Peek('fDirectory',0,'TempFiles');

//Load data' log if equals current file
       TempLog:
LOAD lDirectory, lLastUpdate, '1' as registered resident Log
where lDirectory = '$(vfDirectory)';

Let vlLastUpdate   = Peek('lLastUpdate',0,'TempLog');

IF ('$(lDirectory)' <> NULL() and '$(registered)' = 1) THEN
IF '$(vfLastUpdate)' <> '$(vlLastUpdate)' THEN

//Temp transactions
                 TempTrx:
LOAD CodVenda, CoFar, CodiPro, DataTransacao , Quantidade, Total
FROM '$(File)'(ooxml, embedded labels);

//Sore current file on transaction table
Store CodVenda, CoFar, CodiPro, DataTransacao, Quantidade, Total From TempTrx into Transactions;

//Store on the log
Store fDirectory as lDirectory, fLastUpdate as lLastUpdate From TempFiles Into (txt);

ENDIF
ELSE

Concatenate (Transactions)
TempTrx:
LOAD CodVenda, CoFar, CodiPro, DataTransacao, Quantidade, Total
FROM '$(File)'(ooxml, embedded labels);

//Store on the log
Store fDirectory as lDirectory, fLastUpdate as lLastUpdate From TempFiles Into (txt);

ENDIF

next File

next Ext

for each Dir in dirlist (Root&'\*' )

call DoDir (Dir)

next Dir

end sub
call DoDir ('C:\TST01\Dados');

theoretically I need to do this:

IF Registered = YES THEN

IF LastUpdateLog <> LastUpdateCurrentFile THEN

/*UPDATE THE TABLE WITH ALL FILES ON THE DIRECTORY*/

/*UPDATE LOG*/

END

ELSE

/*PARTIAL UPDATE - UPDATE TABLE INSERT ONLY DATA OF CURRENT FILE*/

/*REGISTERED ON LOG*/

END

SOME ISSUES:

- A VARIABLE vlLastUpdate NO RETURNS VALUE consequently the clauses "IF" do not work

- I'm using a txt to store transactions however if total charge will be necessary to delete the data and make a new reload (not sure if this is the right way)

Obs.: The used files are attached

I hope you can help me.

tks a lot.

topic Check files on the directory and load total/partial in QlikView

Check files on the directory and load total/partial