Relate data to year

[#1]

Hi,

I wonder if you can give data a specific year after it has been loaded into qv, or is it possible to do this in the script? The data is from several excel-sheets and its not related to a specific year in the sheets, but all the data shall be related to the same year. The data comes from many excel-files so this will be a better way than update all files.

[IAMDV]

Yes, this can be done in Load Script. If you have same field names then QlikView will automatically concatenate the fields and you will see only one table. However, if the names are different then you use forced concatenation. You can also add a column (Year) on the fly within your Load Script.

I hope this helps!

Cheers - DV

[#3]

Thanks, but can you explain further? there are no column (Year) in the excelfiles, all data is concatenated in one table. what i want to do is to relate this data with a year, for example 2011.

[erichshiino · Accepted solution]

Hi,

It may depend on how you want to include this year.

In the middle of the load statement, you can just include the year

Load

a, b, c,

2011 as year]

from ...

or you can use the function filename() of filebasename()  ( you can get more information in the QV Help) to get the year from the file name

Let us say that the files have names like:

Sales2010.xls, Sales 2011.xls.

You load script could be like this:

LOAD a,b, num(right( filebasename(), 4)) as Year

FROM

[Sales*.xls]

(biff, embedded labels, table is Sales$);

Hope this helps,

Erich

[IAMDV]

Erich's solutions should work.

Good luck!

Cheers - DV

[#6]

Thanks DV and Erich,

it´s working fine

/Anders