topic Re: Loading data multiple files question in QlikView

Loading data multiple files question

Thu, 20 Mar 2014 23:59:16 GMT

Hi,

I'm loading multiple files into my Load Script by means of using an * after my file names.

I'm having a problem with some of my fields.

I want to break down Year, Month, Day, Time into separate fields from another field but my script is only doing this for the first file.

Does anyone know how I can do this to all files? I think I need to somehow implement the Year[DateTimeNoSec] into the actual LOAD area and not the LOAD* area but can't figure out exactly how to do it correctly and make my filters not show duplicate information.

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*,

Year([DateTimeNoSec]) as Year,

Month([DateTimeNoSec]) as Month,

Day([DateTimeNoSec]) as Day,

Time([DateTimeNoSec]) as Time;

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,

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: Loading data multiple files question

chiru_thota — Fri, 21 Mar 2014 00:22:39 GMT

You can do it some thing like this.

Step1:

Table1:

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

/* Other fields */

FROM.\ProcessedData_oAuth_*.txt

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

Table 2:

Load

LOAD*,

Year([DateTimeNoSec]) as Year,

Month([DateTimeNoSec]) as Month,

Day([DateTimeNoSec]) as Day,

Time([DateTimeNoSec]) as Time from resident Table1:

drop table Table1;

Re: Loading data multiple files question

Fri, 21 Mar 2014 00:38:22 GMT

Thank you for your help. Unfortunately I don't follow your instructions.

Re: Loading data multiple files question

Fri, 21 Mar 2014 00:42:16 GMT

I put in the following code...

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

This works but when I create a List Box, I get 60 entries for the same value. For example, for the date of March 20th, my List box has 60 fields of the number 20.

Re: Loading data multiple files question

chiru_thota — Fri, 21 Mar 2014 00:54:32 GMT

The reason is your taking time stamp. even though date is same time stamp will be different.

may be you can try below.

Timestamp(mid(@4, 2, 17),'MM/DD/YYYY') as Day,

Otherwise up load your file with some data.I can do it for you

Re: Loading data multiple files question

Fri, 21 Mar 2014 01:14:39 GMT

Thank you so much.

I would really like to send you the information because it's work related (sensitive?) and risk getting in trouble.

I arranged my LOAD script as per below.

Everything looks okay but as you mentioned, my List Boxes for Year, Month, and Day show 60 times for the same value. Time is okay since there are 60 minutes in each hour, although it would be even better to split it up by Hour and Minute.

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,

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

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

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

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

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

Re: Loading data multiple files question

Fri, 21 Mar 2014 01:37:07 GMT

I did it! Thank you so much for your tips.

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,