Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Convert Qtr to Year

I have the file format as below

  

CodeQ415 Seats Q116 Seats Q216 Seats Q316 Seats Q416 Seats Q117 Seats Q217 Seats Q317 Seats Q417 Seats
00000
AMS001327327327327323411411411411
ATL001000000000
ATL002343434343434343434
AKL001777778888
ASQ001888888888
BLR0030293030300
BLR00200181800000

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

Thanks much

1 Solution

Accepted Solutions
rwunderlich

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

View solution in original post

5 Replies
sergio0592
Specialist III
Specialist III

Try with:

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

galax_allu
Specialist
Specialist

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
Specialist
Specialist

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

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
Creator
Creator
Author

Awesomeness.

Thanks much Rob