topic Re: Convert Qtr to Year in QlikView

Convert Qtr to Year

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Convert Qtr to Year

sergio0592 — Mon, 22 May 2017 17:46:12 GMT

Try with:

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

Re: Convert Qtr to Year

Anonymous — Mon, 22 May 2017 17:48:55 GMT

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

Re: Convert Qtr to Year

santiago_respane — Mon, 22 May 2017 18:33:51 GMT

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,

Re: Convert Qtr to Year

rwunderlich — Mon, 22 May 2017 19:11:35 GMT

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

Re: Convert Qtr to Year

apthansh — Tue, 23 May 2017 12:15:21 GMT

Awesomeness.

Thanks much Rob