topic Re: loading data in QlikView

loading data

Wed, 06 Jan 2016 19:54:06 GMT

Hello, I am trying to load the data from a file but i don't understand how to do it. I have files named as

filename _year_month_date.qvd. now I have to load all the data from august 2015 to jan 2016. I dont understand how to apply conditions on it. please help

Re: loading data

rikrdodata — Wed, 06 Jan 2016 20:19:08 GMT

That depends on your data type. A simply reload would be like:

Dates:

Load

Year,

Month,

Date

From

_year_month_date.qvd (qvd);

where the first line "Dates" is the name wich will recive your table on qlikview.

if you need to make a condition to load the table, you need to use "where" sentence and here is where it depends on your data type, for example:

Dates:

Load

Year,

Month,

Date

From

_year_month_date.qvd (qvd)

where Year >=2011 and Year <=2015

;

in this load you'll recive only data from 2011 to 2015 excludin all other data.

another example:

Dates:

Load

Year,

Month,

Date

From

_year_month_date.qvd (qvd)

where wildmatch(Month,1,2,3,4,5) and year = 2016

in this load you'll recive only data of year 2016 only month 1 to 5. excludin all other data. (depends on what kind of data you have on Month, it coul be text ('jan',feb,mar) or numbers(1,2,3,4).

Hope it helps.

Ricardo

Re: loading data

Peter_Cammaert — Wed, 06 Jan 2016 20:37:44 GMT

filename_year_month_date.qvd

What exactly are the values for filename and date? Do they change randomly? Can filename have underscore characters as well?

Re: loading data

maxgro — Wed, 06 Jan 2016 20:40:28 GMT

If I understand the question you can use wildcard to load many files with a single load

This is from Qlik Help

The filename may contain the standard DOS wildcard characters ( * and ? ). This will cause all the matching files in the specified directory to be loaded.

Re: loading data

Peter_Cammaert — Wed, 06 Jan 2016 20:46:23 GMT

The technique you can use will be something like (in pseudocode):

Set StartDate and EndDate

Set CurrentDate = StartDate

DO WHILE CurrentDate <= EndDate

Set CurrentYear = Year(CurrentDate)

Set CurrentMonthName =Date(CurrentDate, 'MMM')

MyTable:

LOAD *

FROM [$(FilePath)\*_$(CurrentYear)_$(CurrentMonthName)_*.qvd] (qvd);

Set CurrentDate = AddMonths(CurrentDate, 1);

LOOP

You'll have to add a test for existence if some files may be missing.

Best,

Peter

Re: loading data

Wed, 06 Jan 2016 20:49:25 GMT

filename_201512307301.qvd

where 2015 12 30 represents the day.

Now if I put a 201* it gives all the data from 2010 to 2016

if i put 2015* so it gives data for 2015

but what I want is data after july 2015 to jan 2016.

Re: loading data

maxgro — Wed, 06 Jan 2016 21:34:54 GMT

for y=2015 to 2016

for m=1 to 12

if ( $(y)=2015 and $(m)>7 or $(y)=2016 and m<2) then

let file = 'filename_$(y)' & num($(m), '00') & '*.qvd';

trace $(file);

DATA:

load * from [$(file)] (qvd);

endif;

next

Re: loading data

MarcoWedel — Wed, 06 Jan 2016 21:37:59 GMT

Hi,

maybe like this:

LOAD * From filename_*.qvd (qvd)
Where Date#(Mid(FileBaseName(),10,8),'YYYYMMDD') >= MakeDate(2015,8) and Date#(Mid(FileBaseName(),10,8),'YYYYMMDD') <= MakeDate(2016,1,31)

hope this helps

regards

Marco