Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
pmanu4112
Contributor III
Contributor III

Automatic upload and append of excel files using incremental load. I want to get rid of duplicates in the process of doing this!!!!!Please help experts

Dear experts,

I am having issues in using automatic upload and append to existing QVD. Also I am finding some duplicate records when this runs. I do Not want any duplicate records.

  ---------Script I am using-------------------------------------

Let FileCount = 0;

Let MinFileDate = Date(Today(),'YYYY-MM-DD');

for each file in FileList('\\lkmsappbi01\QlikViewSourceDocs\dev\fhcvce\20_MetaData\Pyxis project\*.xlsx');

Let FileDate = Date#(left(right('$(file)',24),10),'YYYY-MM-DD');

if $(FileDate)>=$(MinFileDate) THEN

LET FileCount = $(FileCount)+1;

//Trace Loading $(File) ($(FileDate));

Cerner:

LOAD

    [Order Id] &   [Financial Number]& [Administration Start Date/Time] as %key,

  [Financial Number],

     //[Order Id],

     [CDM Number],

     [Order Catalog Short Description],

     [Administration Start Date/Time],

     [Administration Personnel],

     [v. OPID],

     [Admin Result Status],

     //[Admin Event Tag],

     [Dosage Amount],

     [Dosage Unit]

     //[Positive Med Ind],

     //[Patient Positive Ind]

FROM

$(file)

(ooxml, embedded labels, table is Administered);

Concatenate

LOAD %key,

     [Financial Number],

     //[Order Id],

     [CDM Number],

     [Order Catalog Short Description],

     [Administration Start Date/Time],

     [Administration Personnel],

     [v. OPID],

     [Admin Result Status],

     //[Admin Event Tag],

     [Dosage Amount],

     [Dosage Unit]

     //[Positive Med Ind],

    // [Patient Positive Ind]

FROM

\\lkmsappbi01\QlikViewSourceDocs\dev\fhcvce\40_QVDGenerators\pyxas\Cerner_auto.qvd

(qvd)

where Not Exists(%key);

ENDIF

next file;

store Cerner into \\lkmsappbi01\QlikViewSourceDocs\dev\fhcvce\40_QVDGenerators\pyxas\Cerner_auto.qvd(qvd);

exit script;

---------------------------------------End of autoincremental load-------------------------------------------------

qlik.PNG

This picture above is the folder in which the xls file are saved in, the code is suppose to go in the folder and pull the latest file and append it to the already save/previous qvd.

[Administration Start Date/Time] is the most unique field in the dataset.

The picture above shows the string format the file is saved.

I would like all of these xls files in a qvd, and using the incremental load to append the latest file.

Please help with a code, or direction on how to get this to work.


  [Administration Start Date/Time] is the most unique field in the dataset.

Thank you in advance Qlikview gurus.

0 Replies