Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try with:
date#('20'&mid(Field,3,2),'YYYY')
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
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.
Hope this helps.
Kind regards,
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
Awesomeness.
Thanks much Rob