topic Relate data to year in QlikView

Relate data to year

Mon, 11 Jul 2011 10:40:41 GMT

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.

Relate data to year

IAMDV — Mon, 11 Jul 2011 10:45:54 GMT

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

Relate data to year

Mon, 11 Jul 2011 10:56:05 GMT

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.

Relate data to year

erichshiino — Mon, 11 Jul 2011 11:53:28 GMT

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

Relate data to year

IAMDV — Mon, 11 Jul 2011 12:02:14 GMT

Erich's solutions should work.

Good luck!

Cheers - DV

Relate data to year

Mon, 11 Jul 2011 13:04:52 GMT

Thanks DV and Erich,

it´s working fine

/Anders