Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have Date data in excel file in YYYYMMDD format.
Used this function to convert to date format -
Date#('20131230','YYYYMMDD') as Date
I derived Quarter with the below code:
'Q' & Ceil(Month(Date)/3) as Quarter,
But I am getting Q1,Q2,Q3,Q4,Q.
I don't want Q. How to elimate this?
Why it is showing Q even I don't have any blank rows in excel file?
Hi,
You have some Null values in LastDate date column so use this expression
Hope this helps you.
Regards,
jagan.
hi try like this
date(Date#('20131230','YYYYMMDD'),'DD/MM/YYYY') as date,
'Q' & Ceil(Month(Date)/3) as Quarter
Hi arulsettu, Tried. But not working.
can u post your qvw
Maybe you don't have proper date. Can you post your excel file
This is the data:
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | LASTDATE | MODIFYDATE |
EMPNo1 | ENAME1 | CLERK | EMPNo11 | 19830112 | 1100 | 20 | 20150302 | 20140101 | |
EMPNo2 | ENAME2 | SALESMAN | EMPNo3 | 19810220 | 1600 | 300 | 30 | 20150303 | 20140202 |
EMPNo3 | ENAME3 | MANAGER | ENAME8 | 19810501 | 2850 | 30 | 20140303 | ||
EMPNo4 | ENAME4 | MANAGER | ENAME8 | 19810609 | 2450 | 10 | 20140404 | ||
EMPNo5 | ENAME5 | ANALYST | ENAME7 | 19811203 | 3000 | 20 | 20150306 | 20140505 | |
EMPNo6 | ENAME6 | CLERK | EMPNo3 | 19811203 | 950 | 30 | 20150307 | 20140606 | |
EMPNo7 | ENAME7 | MANAGER | ENAME8 | 19810402 | 2975 | 20 | 20140707 | ||
EMPNo8 | ENAME8 | PRESIDENT | NULL | 19811117 | 5000 | 10 | 20140808 | ||
EMPNo9 | ENAME9 | SALESMAN | EMPNo3 | 19810928 | 1250 | 1400 | 30 | 20150310 | 20140909 |
EMPNo10 | ENAME10 | CLERK | EMPNo4 | 19820123 | 1300 | 10 | 20150311 | 20141010 | |
EMPNo11 | ENAME11 | ANALYST | ENAME7 | 19821209 | 3000 | 20 | 20141111 | 20141111 | |
EMPNo12 | ENAME12 | CLERK | EMPNo5 | 19801217 | 800 | NULL | 20 | 20150313 | 20141212 |
EMPNo13 | ENAME13 | SALESMAN | EMPNo3 | 19810908 | 1500 | 0 | 30 | 20150314 | 20140113 |
EMPNo14 | ENAME14 | SALESMAN | EMPNo3 | 19810222 | 1250 | 500 | 30 | 20150315 | 20140214 |
EMPNo15 | ENAME15 | SALESMAN | EMPNo5 | 19801217 | 800 | 20 | 20140315 | ||
EMPNo16 | ENAME16 | CLERK | EMPNo6 | 19810908 | 1500 | 300 | 30 | 20150317 | 20140416 |
EMPNo17 | ENAME17 | ANALYST | ENAME8 | 19810222 | 1250 | 30 | 20140517 | 20140517 | |
EMPNo18 | ENAME18 | CLERK | EMPNo5 | 19801217 | 800 | 10 | 20150319 | 20140618 | |
EMPNo19 | ENAME19 | SALESMAN | EMPNo3 | 19810908 | 1500 | 20 | 20150320 | 20140719 | |
EMPNo20 | ENAME20 | SALESMAN | EMPNo3 | 19810222 | 1250 | 30 | 20150321 | 20140820 | |
EMPNo21 | ENAME21 | SALESMAN | EMPNo7 | 19801217 | 800 | 20 | 20150322 | 20140921 | |
EMPNo22 | ENAME22 | CLERK | EMPNo8 | 19810908 | 1500 | 10 | 20150323 | 20141022 | |
EMPNo23 | ENAME23 | ANALYST | ENAME9 | 19810222 | 1250 | 1400 | 30 | 20150324 | 20141123 |
EMPNo24 | ENAME24 | CLERK | EMPNo5 | 19801217 | 800 | 10 | 20141224 | ||
EMPNo25 | ENAME25 | SALESMAN | EMPNo3 | 19810908 | 1500 | 20 | 20150303 | 20140125 | |
EMPNo26 | ENAME26 | SALESMAN | EMPNo3 | 19810222 | 1250 | 50 | 20 | 20150304 | 20140226 |
EMPNo27 | ENAME27 | SALESMAN | EMPNo9 | 19801217 | 800 | 0 | 30 | 20150305 | 20140327 |
EMPNo28 | ENAME28 | CLERK | EMPNo10 | 19810908 | 1500 | 500 | 30 | 20150306 | 20140428 |
EMPNo29 | ENAME29 | ANALYST | ENAME10 | 19810222 | 1250 | 20 | 20150307 | 20140501 | |
EMPNo30 | ENAME30 | CLERK | EMPNo5 | 19801217 | 800 | 300 | 30 | 20150308 | 20140602 |
EMPNo31 | ENAME31 | SALESMAN | EMPNo3 | 19810908 | 1500 | 30 | 20150309 | 20140703 | |
EMPNo32 | ENAME32 | SALESMAN | EMPNo3 | 19810222 | 1250 | 10 | 20150310 | 20140804 | |
EMPNo33 | ENAME33 | SALESMAN | EMPNo11 | 19801217 | 800 | 20 | 20140905 | ||
EMPNo34 | ENAME34 | CLERK | EMPNo12 | 19810908 | 1500 | 30 | 20150312 | 20141006 | |
EMPNo35 | ENAME35 | ANALYST | ENAME11 | 19810222 | 1250 | 20 | 20150313 | 20141107 | |
EMPNo36 | ENAME36 | CLERK | EMPNo5 | 19801217 | 800 | 10 | 20150314 | 20141208 | |
EMPNo37 | ENAME37 | SALESMAN | EMPNo3 | 19810908 | 1500 | 1400 | 30 | 20150315 | 20140109 |
EMPNo38 | ENAME38 | SALESMAN | EMPNo3 | 19810222 | 1250 | 10 | 20150316 | 20140210 | |
EMPNo39 | ENAME39 | SALESMAN | EMPNo13 | 19801217 | 800 | 20 | 20140311 | ||
EMPNo40 | ENAME40 | CLERK | EMPNo14 | 19810908 | 1500 | 50 | 20 | 20150318 | 20140412 |
EMPNo41 | ENAME41 | ANALYST | ENAME12 | 19810222 | 1250 | 0 | 30 | 20140513 | 20140513 |
EMPNo42 | ENAME42 | CLERK | EMPNo5 | 19801217 | 800 | 500 | 30 | 20140614 | 20140614 |
EMPNo43 | ENAME43 | SALESMAN | EMPNo3 | 19810908 | 1500 | 20 | 20150321 | 20140715 | |
EMPNo44 | ENAME44 | SALESMAN | EMPNo3 | 19810222 | 1250 | 300 | 30 | 20150322 | 20140816 |
EMPNo45 | ENAME45 | SALESMAN | EMPNo15 | 19801217 | 800 | 30 | 20150323 | 20140917 | |
EMPNo46 | ENAME46 | CLERK | EMPNo16 | 19810908 | 1500 | 10 | 20141018 | 20141018 | |
EMPNo47 | ENAME47 | ANALYST | ENAME13 | 19810222 | 1250 | 20 | 20141119 | ||
EMPNo48 | ENAME48 | CLERK | EMPNo5 | 19801217 | 800 | 30 | 20150303 | 20141220 | |
EMPNo49 | ENAME49 | SALESMAN | EMPNo3 | 19810908 | 1500 | 20 | 20150304 | 20141121 | |
EMPNo50 | ENAME50 | SALESMAN | EMPNo3 | 19810222 | 1250 | 10 | 20150305 | 20141222 | |
EMPNo51 | ENAME51 | SALESMAN | EMPNo17 | 19801217 | 800 | 1400 | 30 | 20150123 | |
EMPNo52 | ENAME52 | CLERK | EMPNo18 | 19810908 | 1500 | 10 | 20150224 | ||
EMPNo53 | ENAME53 | ANALYST | ENAME14 | 19810222 | 1250 | 20 | 20150308 | 20150325 | |
EMPNo54 | ENAME54 | CLERK | EMPNo5 | 19801217 | 800 | 50 | 20 | 20150309 | 20150426 |
EMPNo55 | ENAME55 | SALESMAN | EMPNo3 | 19810908 | 1500 | 0 | 30 | 20150310 | 20150527 |
EMPNo56 | ENAME56 | SALESMAN | EMPNo3 | 19810222 | 1250 | 500 | 30 | 20150311 | 20150628 |
EMPNo57 | ENAME57 | SALESMAN | EMPNo19 | 19801217 | 800 | 20 | 20150312 | 20150701 | |
EMPNo58 | ENAME58 | CLERK | EMPNo20 | 19810908 | 1500 | 300 | 30 | 20150313 | 20150802 |
EMPNo59 | ENAME59 | ANALYST | ENAME15 | 19810222 | 1250 | 30 | 20150314 | 20150903 | |
EMPNo60 | ENAME60 | CLERK | EMPNo5 | 19801217 | 800 | 10 | 20151004 | 20151004 | |
EMPNo61 | ENAME61 | SALESMAN | EMPNo3 | 19810908 | 1500 | 20 | 20150316 | 20151105 | |
EMPNo62 | ENAME62 | SALESMAN | EMPNo3 | 19810222 | 1250 | 30 | 20150317 | 20151206 | |
EMPNo63 | ENAME63 | SALESMAN | EMPNo21 | 19801217 | 800 | 20 | 20150318 | 20150107 | |
EMPNo64 | ENAME64 | CLERK | EMPNo22 | 19810908 | 1500 | 10 | 20150319 | 20150208 | |
EMPNo65 | ENAME65 | ANALYST | ENAME16 | 19810222 | 1250 | 1400 | 30 | 20150320 | 20150309 |
EMPNo66 | ENAME66 | CLERK | EMPNo5 | 19801217 | 800 | 10 | 20150321 | 20150410 | |
EMPNo67 | ENAME67 | SALESMAN | EMPNo3 | 19810908 | 1500 | 20 | 20150322 | 20150511 | |
EMPNo68 | ENAME68 | SALESMAN | EMPNo3 | 19810222 | 1250 | 50 | 20 | 20150323 | 20150612 |
EMPNo69 | ENAME69 | SALESMAN | EMPNo23 | 19801217 | 800 | 0 | 30 | 20150713 | |
EMPNo70 | ENAME70 | CLERK | EMPNo24 | 19810908 | 1500 | 500 | 30 | 20150302 | 20150814 |
EMPNo71 | ENAME71 | ANALYST | ENAME17 | 19810222 | 1250 | 20 | 20150303 | 20150915 | |
EMPNo72 | ENAME72 | CLERK | EMPNo5 | 19801217 | 800 | 300 | 30 | 20150304 | 20151016 |
EMPNo73 | ENAME73 | SALESMAN | EMPNo3 | 19810908 | 1500 | 30 | 20150305 | 20151117 | |
EMPNo74 | ENAME74 | SALESMAN | EMPNo3 | 19810222 | 1250 | 10 | 20150306 | 20151218 | |
EMPNo75 | ENAME75 | SALESMAN | EMPNo25 | 19801217 | 800 | 20 | 20150307 | 20150119 | |
EMPNo76 | ENAME76 | CLERK | EMPNo26 | 19810908 | 1500 | 30 | 20150220 | ||
EMPNo77 | ENAME77 | ANALYST | ENAME18 | 19810222 | 1250 | 20 | 20150309 | 20150321 | |
EMPNo78 | ENAME78 | CLERK | EMPNo5 | 19801217 | 800 | 10 | 20150310 | 20150422 | |
EMPNo79 | ENAME79 | SALESMAN | EMPNo3 | 19810908 | 1500 | 1400 | 30 | 20150311 | 20150523 |
EMPNo80 | ENAME80 | SALESMAN | EMPNo3 | 19810222 | 1250 | 10 | 20150312 | 20150624 | |
EMPNo81 | ENAME81 | SALESMAN | EMPNo27 | 19801217 | 800 | 20 | 20150313 | 20150725 | |
EMPNo82 | ENAME82 | CLERK | EMPNo28 | 19810908 | 1500 | 50 | 20 | 20150314 | 20150826 |
EMPNo83 | ENAME83 | ANALYST | ENAME19 | 19810222 | 1250 | 0 | 30 | 20150315 | 20150927 |
EMPNo84 | ENAME84 | CLERK | EMPNo5 | 19801217 | 800 | 500 | 30 | 20150316 | 20151028 |
EMPNo85 | ENAME85 | SALESMAN | EMPNo3 | 19810908 | 1500 | 20 | 20151101 | 20151101 | |
EMPNo86 | ENAME86 | SALESMAN | EMPNo3 | 19810222 | 1250 | 300 | 30 | 20151202 | 20151202 |
EMPNo87 | ENAME87 | SALESMAN | EMPNo29 | 19801217 | 800 | 30 | 20150319 | 20150103 | |
EMPNo88 | ENAME88 | CLERK | EMPNo30 | 19810908 | 1500 | 10 | 20150320 | 20150204 | |
EMPNo89 | ENAME89 | ANALYST | ENAME20 | 19810222 | 1250 | 20 | 20150321 | 20150305 | |
EMPNo90 | ENAME90 | CLERK | EMPNo5 | 19801217 | 800 | 30 | 20150322 | 20150406 | |
EMPNo91 | ENAME91 | SALESMAN | EMPNo3 | 19810908 | 1500 | 20 | 20150323 | 20150507 | |
EMPNo92 | ENAME92 | SALESMAN | EMPNo3 | 19810222 | 1250 | 10 | 20150317 | 20150608 | |
EMPNo93 | ENAME93 | SALESMAN | EMPNo31 | 19801217 | 800 | 1400 | 30 | 20150318 | 20150709 |
EMPNo94 | ENAME94 | CLERK | EMPNo32 | 19810908 | 1500 | 10 | 20150319 | 20150810 | |
EMPNo95 | ENAME95 | ANALYST | ENAME21 | 19810222 | 1250 | 20 | 20150320 | 20150911 | |
EMPNo96 | ENAME96 | CLERK | EMPNo5 | 19801217 | 1250 | 50 | 20 | 20150321 | 20151012 |
EMPNo97 | ENAME97 | SALESMAN | EMPNo3 | 19810908 | 800 | 0 | 30 | 20150322 | 20151113 |
EMPNo98 | ENAME98 | SALESMAN | EMPNo3 | 19810222 | 1500 | 500 | 30 | 20150323 | 20151214 |
EMPNo99 | ENAME99 | SALESMAN | EMPNo33 | 19801217 | 1250 | 1400 | 20 | 20150317 | 20151115 |
EMPNo100 | ENAME100 | SALESMAN | EMPNo33 | 19801217 | 1250 | 1400 | 20 | 20150317 | 20151216 |
check this
You have blanks in Last date
Hi,
You have some Null values in LastDate date column so use this expression
Hope this helps you.
Regards,
jagan.