topic Re: Multiple file upload creating Syntax in QlikView

Multiple file upload creating Syntax

Fri, 21 Mar 2014 23:05:53 GMT

Hi,

Can anyone help me with my LOAD Script?

I am doing a multiple file upload but it is removing some lines as QlikView does not recognize these files being the 'same', creating a Syntax. Below is my LOAD Script.

I believe it is using my first data as the 'main' data and then trying to consolidate data.

I believe the error is somewhere at the top (in bold).

Please help!!!

LOAD*,

if(index('.jpg.ico.gif.png',Extension) > 0, 'Yes', 'No') as IsImage;

LOAD*,

if(index(Page, '.', -1) > 0, mid(Page, index(Page, '.', -1)), '') as Extension;

LOAD*,

if(index(URL, '?') > 0, mid(URL,1, index(URL, '?') -1), URL) as Page,

if(index(URL, '?') > 0, mid(URL,index(URL, '?')+1), '') as Params;

LOAD*,

daystart(DateTime) as Date,

mid(RequestURI, index(RequestURI, ' ', 1), index(RequestURI, ' ', 2) - index(RequestURI, ' ', 1)) as URL,

mid(RequestURI, index(RequestURI, ' ', 2)) as Protocol,

if(index(Track, '/', 3) > 0, mid(Track, index(Track, '/', 2) + 1, (index(Track, '/', 3) - index(Track, '/', 2)) - 1), '') as ReferrerSite;

LOAD

filename() as DateHour,

@1 as RemoteHost,

date(date#(mid(@4, 2, 21), 'DD/MMM/YYY:hh:mm:ss'), 'DD MMM YYYY hh:mm:ss') as DateTime,

date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD/MMM/YYYY hh:mm') as DateTimeNoSec,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'YYYY')) as Year,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'MMM')) as Month,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD')) as Day,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'hh')) as Hour,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'mm')) as Minute,

replace(@5, ']', '') as TimeZone,

@2 as FromIdentifier,

@3 as RemoteUser,

@4 as RequestTime,

mid(@5, 1, index(@5, ' ', 1)) as Method,

@5 as RequestURI,

@6 as HTTPstatus,

@7 as Bytes,

@8 as ForwardedIP,

@9 as Track,

@10 as RemoteHostIP,

@11 as ResponseTimeinMS

FROM

.\ProcessedData_oAuth_*.txt

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

Re: Multiple file upload creating Syntax

Gysbert_Wassenaar — Sat, 22 Mar 2014 08:03:48 GMT

The preceding loads are only applied to the first .txt file. You first want to load the data from all the files into one table and then create the other fields in another load statement using a resident load (or store the intermediate table to a qvd first and load from that qvd).

Re: Multiple file upload creating Syntax

stevedark — Sat, 22 Mar 2014 08:14:41 GMT

Loading all files into a single temporary table before doing the preceding loads on a resident load (as Gysbert suggests) will work.

I tend to avoid the issue though by not using * to load from multiple files, if you change the code to load each file at a time using a loop then the preceding load does not cause problems:

for each vFile in FileList('.\ProcessedData_oAuth_*.txt')

LOAD

if(index('.jpg.ico.gif.png',Extension) > 0, 'Yes', 'No') as IsImage

;

[rest of your load here]

@11 as ResponseTimeinMS

FROM

$(vFile)

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

next

There are other advantages of this approach, including the fact you can add extra logic inside the loop - perhaps a trace statement to say which file is being loaded or an if statement to avoid some files (eg. ~ prefixed files when loading from XLS).

Hope that helps.

Steve