topic Re: Dynamic loading of text file in QlikView

Dynamic loading of text file

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

Dear QLikview Experts,

Recently I had came into a big headache with the following situation whereby my machine system had been modified by adding in extra data to the text file (The extra data is 'Retest'). In addition, some of the machines never standardize the format of saving, resulting this extra data being stored in different columns as compared to other machines when loading into Qlikview.

I had provide sample for the situation I was facing now in the attachment and I will paste my current script in below for your reference. All the way I was using incremental load for daily Qlikview reload. But after the extra data being added to the text file, my script was no longer be able to work. I had been working on this for a couple of days and I couldn't figure out how to improve on my current script. Any advises from you all would be greatly appreciated! Thanks a lot in advance!

SUB Scanfolder(Root)

let ControlQVD = left(Root,Index(Root,'\',-1))&'control.qvd';
Let SummaryQVD = left(Root,Index(Root,'\',-1))&'SummaryTable.qvd';

If FileSize(ControlQVD) then
control:load filename, timestamp from [$(ControlQVD)](qvd);
else
control:load '' as filename, now() as timestamp AutoGenerate 0; // EmptyTable
Endif

For each FileExtension in 'txt'
For each FoundFile in FileList(Root &'\AABB_????_???_????.'& FileExtension)

Let t = alt(FieldIndex('filename','$(FoundFile)'),0);
If t = 0 then
control: load '$(FoundFile)' as filename, now() as timestamp AutoGenerate 1; // Keep record of the newly added file;

SummaryTable:
LOAD
    @1 as Date,
    @2 as Type,
    @3 as Result

FROM [$(FoundFile)]
(txt, codepage is 1252, no labels, delimiter is ';', msq);
Endif
Next FoundFile
Next FileExtension

If NoOfRows('control') > 0 then // Check if the control table has been incremented?
store control into [$(ControlQVD)](qvd);
Drop Table control;
Endif

If NoOfRows('SummaryTable') > 0 then // Check if the summary table has been incremented?
If FileSize('$(SummaryQVD)') > 0 then
Concatenate(SummaryTable) load * from [$(SummaryQVD)](qvd);
Endif
store SummaryTable into [$(SummaryQVD)](qvd);
Drop Table SummaryTable;
Endif

End Sub

Call ScanFolder('C:\Users\User\Desktop\Machine\Machine1'); //Loop through all the Machine folders
Call ScanFolder('C:\Users\User\Desktop\Machine\Machine2');
Call ScanFolder('C:\Users\User\Desktop\Machine\Machine3');

IncrementalLoad:
LOAD

     Date,
     Type,
     Result

FROM

(qvd);

Best Regards,

QianNing

Re: Dynamic loading of text file

mariusz_kumansk — Sat, 16 Dec 2017 14:16:44 GMT

Your FileList mask is incorrect. Change in your script this line:

For each FoundFile in FileList(Root &'\AABB_???_????_???_????.txt')

Re: Dynamic loading of text file

s10157754 — Sun, 17 Dec 2017 02:21:48 GMT

Dear Kumanski,

Thanks for you reply. I did changed the coding in my script but I am still unable to load the new data ('Retest') into my Qlikview data model. Any other suggestions? Thanks a lot for your help!

SUB Scanfolder(Root)

let ControlQVD = left(Root,Index(Root,'\',-1))&'control.qvd';

Let SummaryQVD = left(Root,Index(Root,'\',-1))&'SummaryTable.qvd';

If FileSize(ControlQVD) then

control:load filename, timestamp from [$(ControlQVD)](qvd);

else

control:load '' as filename, now() as timestamp AutoGenerate 0; // EmptyTable

Endif

For each FileExtension in 'txt'

For each FoundFile in FileList(Root &'\AABB_???_????_???_????.'& FileExtension)

Let t = alt(FieldIndex('filename','$(FoundFile)'),0);

If t = 0 then

control: load '$(FoundFile)' as filename, now() as timestamp AutoGenerate 1; // Keep record of the newly added file;

SummaryTable:

LOAD

@1 as Date,

@2 as Type,

@3 as Result

FROM [$(FoundFile)]

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

Endif

Next FoundFile

Next FileExtension

If NoOfRows('control') > 0 then // Check if the control table has been incremented?

store control into [$(ControlQVD)](qvd);

Drop Table control;

Endif

If NoOfRows('SummaryTable') > 0 then // Check if the summary table has been incremented?

If FileSize('$(SummaryQVD)') > 0 then

Concatenate(SummaryTable) load * from [$(SummaryQVD)](qvd);

Endif

store SummaryTable into [$(SummaryQVD)](qvd);

Drop Table SummaryTable;

Endif

End Sub

Call ScanFolder('C:\Users\User\Desktop\Machine\Machine1'); //Loop through all the Machine folders

Call ScanFolder('C:\Users\User\Desktop\Machine\Machine2');

Call ScanFolder('C:\Users\User\Desktop\Machine\Machine3');

IncrementalLoad:

LOAD

Date,

Type,

Result

FROM

(qvd);

Re: Dynamic loading of text file

mariusz_kumansk — Sun, 17 Dec 2017 19:06:01 GMT

Because you have a different structure of your files. I added a simple switch that counts columns in your file and loads data. You should use a tab in your script to make the code more readable (like in the attached file).

FieldCount:

LOAD

[@1:n] as line

FROM

[$(FoundFile)]

(fix, codepage is 1252, no labels);

LET vCount = null();

// count columns

LET vCount = SubStringCount(Peek('line',0,'FieldCount'), ';');

LET vLine = null();

LET vLine = Peek('line',0,'FieldCount');

// drop table

DROP Table FieldCount;

TRACE ' ============== Columns: $(vCount) ==============';

TRACE '$(vLine)';

TRACE ' =========================================';

IF vCount = 2 THEN

SummaryTable:

LOAD

@1 as Date,

@2 as Type,

@3 as Result,

null() as NewField

FROM

[$(FoundFile)]

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

ENDIF

IF vCount = 3 THEN

SummaryTable:

LOAD

@1 as Date,

@2 as Type,

@3 as Result,

@4 as NewField

FROM

[$(FoundFile)]

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

ENDIF

Re: Dynamic loading of text file

s10157754 — Sun, 17 Dec 2017 22:00:17 GMT

Dear Kumanshiki,

Thanks a lot for your solution! As I was using Qlikview Personal Edition and I am not able to open your QVW. Are you able to paste the full code in the reply? Thanks a lot in advance!

Best Regards

QianNing

Re: Dynamic loading of text file

mariusz_kumansk — Sun, 17 Dec 2017 22:04:38 GMT

Sure!

Re: Dynamic loading of text file

s10157754 — Sun, 17 Dec 2017 22:16:47 GMT

Dear Kumanshi,

Thanks so much for your help! You had solved my problem, appreciated a lot for your kind effort!

Best Regards

QianNing

Re: Dynamic loading of text file

mariusz_kumansk — Sun, 17 Dec 2017 22:27:12 GMT

Remember to mark this topic as resolved. thanks!

Re: Dynamic loading of text file

s10157754 — Mon, 18 Dec 2017 02:31:48 GMT

Loading data from a specific date onwards