incremental load based on the file name

[rafael_gerhardt]

‌

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

[Chanty4u]

chk dis

Incremental Load in Qlikview - Sources

[HirisH_V7]

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

[vinieme12]

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);

[effinty2112 · Accepted solution]

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

[rafael_gerhardt]

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);