Append and history file

[livio218526]

I have a big problem.

I have files to load from a directory called FilePath. I need to check the history of files already loaded so I can update my table every time I insert a new file in the FilePath directory.

An additional complication is that the new file must diffuse from the old based on:

- a combination of 3 fields (SCENARIO, YEAR, PERIOD) that we can call KEY;

- a version of the file given by the date and a progressive one.

An example of a name is: ACT_2018_01_20180131_00, while a different file can be ACT_2018_01_20180131_01.

If Scenario, Year and Period are the same then you have to replace them, while if the new file has a different scenario, year and period, then you have to go into append.

If there are no new files uploaded to the directory then I have to show only those already loaded.

Thanks

[Anonymous]

Hi, I didn't get exactly the logic of the exclusion part, but as for working with the files I'd read all the folder with a * and only read the filename

Load FileBaseName()

From dir/*.ext

Then you will have a table with all filenames.

After that I'd keep on a table all the valid names that already exist and load the files like this:

Load *

From dir/*.ext

Where not exists(actualFileBaseName,SavedFileBaseName)

Hope it helps!

Cheers

[livio218526]

Sorry,

but I could have file with same Scenario,Year and Period but a different Progressive,

for example: ACT_2017_08_20170801_00 and ACT_2017_08_20170801_01 and I want replace data, instead if I have a file name like ACT_2017_12_20170801_00 i want concatenate the file that just exist from first load.

[Anonymous]

You can check that stuff with the FileBaseName and subfield clauses

[maxgro]

If I understand:

Load the distinct file names from history and from the directory

in a table with two fields

    field1, field2

    ACT_2018_01_20180131, 00

    ACT_2018_01_20180131, 01

    ACT_2017_08_20170801, 01

Left join this table grouping by field 1 to find the max of field2 (by field1) and add a flag field

You get a table with the file to load (flag)

    field1, field2, flag

    ACT_2018_01_20180131, 00,

    ACT_2018_01_20180131, 01, 1

    ACT_2017_08_20170801, 01, 1

Load from history and from the directory the record or files with the flag = 1

[livio218526]

This is an example, in the first load i upload File 1, on the secon i upload the file 2 in append and finally i upload file 3 but in replace because the file just contai Scenario,Year and Month

[maxgro]

Script below, files in the attachments

File:

LOAD

    Scenario,

    Year,

    Month,

    Progressivo,

    Amount

FROM

    ACT_????_??_????????_??.txt

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

rk:

Right Keep (File)

LOAD

    Scenario,

    Year,

    Month,

    Max(Progressivo) as Progressivo

Resident File

Group by

    Scenario,

    Year,

    Month;

DROP Table rk;