topic Re: incremental load based on the file name in QlikView

incremental load based on the file name

rafael_gerhardt — Wed, 25 Nov 2020 16:16:04 GMT

‌

Folks,

I'm still learning the QlikView ... I googled but could not write code that works for incremental load based on the file name. Let me explain. I want to make daily load the raw data received (.txt) to QVD files incrementally. These files are named according to the type of document and the last date of the records contained therein. The following exemplary relationship. Could anyone help? Thank you very much in advance.

Rafael.

nc_nc_20160222.TXT

nc_nc_20160223.TXT

nc_nc_20160224.TXT

nc_nc_20160225.TXT

nc_nc_20160226.TXT

nc_nc_20160229.TXT

nc_nc_20160301.TXT

nc_nc_20160302.TXT

nc_nc_20160303.TXT

nc_nc_20160304.TXT

nc_nc_20160307.TXT

nc_nc_20160308.TXT

nc_nc_20160309.TXT

nc_nc_20160310.TXT

nc_nc_20160311.TXT

nc_nc_20160314.TXT

nc_nc_20160315.TXT

nc_nc_20160316.TXT

nc_nc_20160317.TXT

nc_nc_20160318.TXT

nc_nc_20160321.TXT

nc_nc_20160322.TXT

nc_nc_20160323.TXT

Re: incremental load based on the file name

Chanty4u — Sat, 02 Jul 2016 16:56:05 GMT

chk dis

Incremental Load in Qlikview - Sources

Re: incremental load based on the file name

HirisH_V7 — Sat, 02 Jul 2016 17:29:05 GMT

Hi,

Check this,

Data:
LOAD Customer,
    [Sales Order ID],
    ShipDate as Date,
    Month(ShipDate) as Month,
    NUm( Month(ShipDate)) as MonthNum,
    Product,
    Sales,
    Quantity
  
FROM
[Sales Orders*.xls]
(biff, embedded labels, table is [Sales Orders$])where Date(filetime(), 'DD/MM/YYYY') >= today();

Hope this will be helpful for you. If you want to pick the file based on current date and time of reload if it happen on daily basis.

else,

Please elaborate your Requirement,

PFA,

Hirish

Re: incremental load based on the file name

vinieme12 — Sat, 02 Jul 2016 17:48:12 GMT

I'm assuming you have still not created a QVD from your existing text files so lets create that first

For each TABLEFILE in filelist ('your_full_directory_path_here\*.TXT')

ALL_DATA:

LOAD

Field1,

Field2

FROM

$(TABLEFILE)

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

Next TABLEFILE

Store Table ALL_DATA in fullpath\QVDNAME.QVD;

Now we have a QVD to append to; so from next day onward run the below script.(Adding to Bottom of table)

let vFilename = 'nc_nc_' & DATE(now(),'YYYYMMDD') & '.TXT';

ALL_DATA:

LOAD *

FROM fullpath\QVDNAME.QVD

(QVD);

CONCATENATE

DailyData:

LOAD

Fiel1,

Field2

FROM

'you_full_path_here\'$(vFilename)

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

Re: incremental load based on the file name

effinty2112 — Sat, 02 Jul 2016 18:59:27 GMT

Hi Raphael,

This script compares the filetime of the txt files with the latest date on a qvd to decide if the txt file's data is loaded.

First of all load the data from your qvd then run a piece of script like this:

TempMaxDate:

LOAD

Max (Due Date)as MaxDate From

Data.qvd

(qvd);

Let vMaxQVDdate = Peek('MaxDate',0,'TempMaxDate');

Drop table TempMaxDate;

This will place the value of the largest date on your qvd into the variable vMaxQVDdate. You can use this variable to choose the txt files to load. Something like:

For Each file in FileList('nc_nc_*.TXT') \\You will need to include the path to your files here

Let vFileDate = Floor(FileTime('$(file)'));

if Floor(FileTime('$(file)')) > $(vMaxQVDdate) then

LOAD

Field1,

Field2,

FROM

[$(file)]

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

END If;

If your qvd and your txt files have the same fields then the data from the txt files will automatically concatenate with the data from your qvd. If not you'll need a little more scripting.

Now if you STORE the data to a new qvd file overwriting the existing one a reload will not add any more data to that on the new qvd unless a new txt file is added to your txt file folder.

Good luck

Andrew

Re: incremental load based on the file name

rafael_gerhardt — Sat, 02 Jul 2016 23:53:08 GMT

THATS GREAT!!! Tks

here are de final script:

// first create QVD

Directory;

NC_dados_STA:

LOAD

date#([@20:27],'yyyyMMdd') as DataLinha,

[@1:n] as DadosTexto

FROM

[..\..\nc_nc_20160222.TXT]

(fix, codepage is 1252, no labels);

store NC_dados_STA into [..\..\NC_dados_STA.qvd] (qvd);

// after load the data from your qvd...

NC_dados_STA:

Directory;

LOAD DataLinha,

DadosTexto

FROM

[..\..\NC_dados_STA.qvd]

(qvd);

//... then create a var max date

TempMaxDate:

LOAD

Max (DataLinha) as MaxDate

From [..\..\Projeto Monitoramento COF\Dados DPF\NC\Extrações\NC_dados_STA.qvd] (qvd);

Let vMaxQVDdate = DATE(Peek('MaxDate',0,'TempMaxDate'),'YYYYMMDD');

Drop table TempMaxDate;

// load the next files...

For Each File in FileList('..\..\Projeto Monitoramento COF\Dados DPF\NC\Extrações\nc_nc_*.TXT')

Let vFileDate = date#(right(Subfield(Subfield('$(File)', '\', SubstringCount('$(File)', '\') + 1), '.', 1),8),'YYYYMMDD');

if DATE($(vFileDate)) > DATE($(vMaxQVDdate)) then

LOAD

date#([@20:27],'yyyyMMdd') as DataLinha,

[@1:n] as DadosTexto

FROM [$(File)] (fix, codepage is 1252, no labels);

END If;

next File

// store the complete database

store NC_dados_STA into [..\..\NC_dados_STA.qvd] (qvd);