Incremental multiple csv files load

[nandhaadjame200]

Hi There

Please help to achieve the below,

I need to load multiple .CSV files on Incremental basis and folder location is same but file name will differ by date suffix and based on the new or updated record in csv files should updated into base qvd (Insert New record, Update existing record). Next time if a new file comes it should be iterated.

Please help to provide the script.

Thanks

Nandhakumar

[nandhaadjame200]

dilipranjith‌

Here is the script i'm using now,

set vRoot = 'LIB://Conn';

set vFirstLoad=0; //set 1 for first load

FOR Each File in filelist ('$(vRoot)'&'\*Contact*.csv')

CurrentFileList:

Load '$(File)' as File

autogenerate 1;

next File

if(vFirstLoad=1) then

MyTable:

LOAD *,Date(Right(Left(FileBaseName(),37),10),'MM/DD/YYYY') as [UpdateDate]

FROM [LIB://Conn\*Contact*.csv]

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

else

OldFileList:

LOAD File as History

FROM [LIB://Conn\FileList.qvd] (qvd);

NoConcatenate

NewFiles:

Load File as NewFile

    resident CurrentFileList

    Where not exists(History, File);

MyTable:

    LOAD *

FROM [LIB://Conn\MyTable.qvd] (qvd);

FOR EACH File in FieldValueList('NewFile')

    Concatenate(MyTable)

    LOAD *,Date(Right(Left(FileBaseName(),37),10),'MM/DD/YYYY') as [UpdateDate]

FROM [$(File)] (txt, codepage is 28591, embedded labels, delimiter is ',', msq);

    next;

END IF

STORE CurrentFileList into [LIB://Conn\FileList.qvd] (qvd);

STORE MyTable into [LIB://Conn\MyTable.qvd] (qvd);

drop table CurrentFileList;

--------------------------------------------------------------------------

The above script loads everything on daily basis. I need another resident table or qvd which should have only unique contact records with all updates applied. Can you help on this?

Thanks

Nandhakumar

[nandhaadjame200]

Hi Juraj & Dilip

Let me explain again,

On 1st Upload (Here Contact ID & Email is unique identifier of an record)

Actual File Name: mpnent_contact_delta_export2018-10-28 000003.000

Day 1

Contact ID, Email, Name

1, x@email.com, x

2, y@email.com,

3, z@email.com, z

Actual File Name: mpnent_contact_delta_export2018-10-29 000003.000

Day 2 - I'll get net new records & existing records with updates

Contact ID, Email, Name

2, y@email.com, y

4, 123@email.com, 123

So, I need to achieve the above scenario on daily basis as mentioned below QVD's,

1. Unique Contact Records in xyz.qvd (Only one record based on unique identifier with latest updates)

2. The Daily updating Mytable.qvd which holds all records on date basis which is already achieved and it is based on your guidance earlier.

xyz.qvd (Unique Records)

-----------------------------------

Contact ID, Email, Name, UploadDate

1, x@email.com, x, 2018-10-28 - Existing record

2, y@email.com, y, 2018-10-29 - With latest update

3, z@email.com, z, 2018-10-28 - Existing record

4, 123@email.com, 123, 2018-10-29 - Recently added record

Mytable.qvd (With Duplicate's)

-----------------------------------------

Contact ID, Email, Name, UploadDate

1, x@email.com, x, 2018-10-28

2, y@email.com, , 2018-10-28

3, z@email.com, z, 2018-10-28

2, y@email.com, y, 2018-10-29

4, 123@email.com, 123, 2018-10-29

Please help on this or I appreciate if any better ideas.

Thanks

Nandhakumar

[dplr-rn]

If i understand it correctly you will get only new and changed records in new excels.

Then its quite straightforward IMHO.

Look at 2. insert and update in this link

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

basically on new reloads you

step 1 load the excel file with new/updated records

Step 2 load the qvd with not exists on contactID

Hope it helps

[nandhaadjame200]

Hi Dilip

I've done as per your guidance. But i'm receiving the following error.

---

The following error occurred:

Field 'a' not found

---

The error occurred here:

?

---

Data has not been loaded. Please correct the error and try loading again.

10:38:39 AM

Following is script after my previous scrip:

if(vFirstLoad=1) then

exit script;

else

LastUpdateDate:

Load Max(Date([UpdateDate])) as MaxDate

Resident MyTable;

Let vUpdateDate = Peek('LastUpdateDate',0,'MaxDate');

Drop table LastUpdateDate;

ContactsTemp:

Load *

Resident MyTable

Where [UpdateDate] > $(vUpdateDate);

Concatenate

Load *

From [LIB://Conn\MyTable.qvd] (qvd)

Where not exists([ContactID]);

STORE ContactsTemp into [LIB://Conn\MyTable.qvd] (qvd);

Drop Table ContactsTemp;

EndIf

Thanks

Nandhakumar

[nandhaadjame200]

Hi Dilip

I've tried but concatenation is not working. Attached the script & sample data.

Please to fix this.

Thanks

Nandhakumar

[dplr-rn · Accepted solution]

Done. See attached script

Basically switched around below section and added a not exists.Logic - you load the new files and then you load the qvd but dont include any updated records

FOR EACH File in FieldValueList('NewFile')

        MyTable:

        LOAD *,Date(Right(Left(FileBaseName(),37),10),'MM/DD/YYYY') as "UpdateDate"

FROM [$(File)] (txt, codepage is 28591, embedded labels, delimiter is ',', msq);

    next;

   // Concatenate(MyTable) this is not needed as it will auto concatenate

    LOAD *

FROM [LIB://Conn\MyTable.qvd] (qvd)

    Where not exists(ContactID);

[callen973]

It is actually quit simple, but the explanation is kind of complex so I will try my best to break it down.

1. Make sure that in all of your csv files the column names are consistent in naming and location.

2. Create a loop to cycle through the directory/folder where your csv files are located. After the first cycle you will need to concatenate.

3. Create a key field. This is important in the incremental load portion as you will not bring in those items from the qvd that are existing in your csv load. This will act as an insert/update.

Example:

DIRECTORY ["Location of the files"];

FOR EACH File in FileList ('*.csv')

temp:
LOAD "Your columns and other derived fields needed"
FROM $(File)
(txt, codepage is 1252, no labels, delimiter is spaces, msq, header is 1 lines);


SET Concatenate = concatenate;

next File

LOAD *
Resident ACHFile;

Concatenate

LOAD *
FROM "Path to qvd file";

store temp into "Path to qvd"(qvd);

drop table temp;

[nandhaadjame200]

Hi Chris

Thanks for your inputs but i tried but still it is not concatenating. Also in above script yo mentioned as "ACHFile", please clarify what is for? And it is still not concatenating the records.

Please review my code based on your help.

Thanks

Nandhakumar

[callen973]

Apologies, I should of replaced ACHFile with temp, and my code base is for QlikView.

[callen973]

set vRoot = 'LIB://Conn';

FOR Each File in filelist ('$(vRoot)'&'\*.csv')
CurrentFileList:
Load '$(File)' as File
(txt, codepage is 28591, embedded labels, delimiter is ',', csv)
autogenerate 1;

SET Concatenate = concatenate;  
next File

if(NOT ISNULL(QvdCreationTime('Path to qvd'))) -- check if qvd exists
RESULTS:
LOAD *
RESIDENT CurrentFileList;

Concatenate

LOAD *
FROM [LIB://Conn\MyTable.qvd] (qvd)
     Where not exists(ContactID);

STORE RESULTS INTO into [LIB://Conn\FileList.qvd] (qvd);
DROP TABLE RESULTS;
DROP TABLE CurrentFileList;
ELSE
STORE CurrentFileList; INTO into [LIB://Conn\FileList.qvd] (qvd);
DROP TABLE CurrentFileList;
END IF