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.
