Order Text Representing Month and Year in Chronological Order

[#1]

Hello,

I have data that represents dates coming out of the database as strings (and stored as a varchar in the database). I am formatting this string on the load statement and get the format I want showing month and year (i.e. Apr-2006). I have this in a qlikview listbox, but now want to order this text in chronological order. I can't seem to find a way. I know to use the properties>sort, but am coming unstuck on an expression that might solve this.

LOAD "CRN",

     If(mid(DISCHARGEDATE,5,2)='01','Jan' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='02','Feb' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='03','Mar' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='04','Apr' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='05','May' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='06','Jun' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='07','Jul' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='08','Aug' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='09','Sep' & '-' & left(DISCHARGEDATE,4),

     If(mi(DISCHARGEDATE,5,2)='10','Oct' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='11','Nov' & '-' & left(DISCHARGEDATE,4),

     If(mid(DISCHARGEDATE,5,2)='12','Dec' & '-' & left(DISCHARGEDATE,4))))))))))))) as DisMonth

Any advise would be greatfully received??

Thanks

,

[swuehl · Accepted solution]

Instead of creating strings that describe your DisMonth, try the date# and date functions to create real date types:

LOAD "CRN",

     date(date#(DISCHARGEDATE,'YYYYMM'),'MMM-YYYY') as DisMonth

DisMonth now have a numerical representation you can sort on, this will give you the values in chronological order.

Hope this helps,

Stefan

edit:

please check out the format codes (in the Help file), you may need or want to modify the code 'YYYYMM' to match your actual used input format.

[#3]

Hello Stefan,

Thanks a lot for your help. That has worked perfectly.

I just tweaked it with a string function to stop some duplication of the month-year output, and bingo!

Thanks again for your swift and helpful response,

Dave.