Convert Qtr to Year

[apthansh]

I have the file format as below

Code	Q415 Seats	Q116 Seats	Q216 Seats	Q316 Seats	Q416 Seats	Q117 Seats	Q217 Seats	Q317 Seats	Q417 Seats
0	0	0	0	0
AMS001	327	327	327	327	323	411	411	411	411
ATL001	0	0	0	0	0	0	0	0	0
ATL002	34	34	34	34	34	34	34	34	34
AKL001	7	7	7	7	7	8	8	8	8
ASQ001	8	8	8	8	8	8	8	8	8
BLR003	0	29	30	30	30	0
BLR002	0	0	18	18	0	0	0	0	0

How can I group this to year ? In the Raw file I have columns from Q415 Seats to Q430 Seats (2015 - 2030)

Thanks much

[sergio0592]

Try with:

date#('20'&mid(Field,3,2),'YYYY')

[Anonymous]

Hi

try to use rename function to change them to Months(or years ..) first

the following link may be usefull..

https://community.qlik.com/blogs/qlikviewdesignblog/2012/09/25/how-to-rename-fields

[santiago_respane]

Hi Ansh, since you are trying to rename fields performing a calculation based on the old name i would go with the rename script function.  This way you can use a dinamic renaming based on a table you can easily mantain from excel or an inline.

Rename field ‒ QlikView

Hope this helps.

Kind regards,

[rwunderlich · Accepted solution]

How about this:

Raw:

CrossTable (Qtr, Seats)

LOAD

  Code,

    [Q415 Seats],

    [Q116 Seats],

    [Q216 Seats],

    [Q316 Seats],

    [Q416 Seats],

    [Q117 Seats],

    [Q217 Seats],

    [Q317 Seats],

    [Q417 Seats]

FROM

[https://community.qlik.com/thread/261360]

(html, codepage is 1252, embedded labels, table is @1);

Final:

LOAD

  Code,

  2000 + mid(Qtr,3,2) as Year,

  Sum(Seats) as Seats

Resident Raw

Group By Code, 2000 + mid(Qtr,3,2)

;

DROP TABLE Raw;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[apthansh]

Awesomeness.

Thanks much Rob