sorting date field ; sorting concatenated field; sorting union of fields

[#1]

Hi All,

I have problem while sorting fields.

I have three queries...

Table1:

select a.acc_display as Partner,b.bip_name as Channel

,c.cur_name as Currency

,sum(fpr.fpr_brl_amount) as TotalCost_Subscriber,sum(fpr.fpr_cal_base) as PayingBase_Subscriber,

case when sum(fpr.fpr_cal_base)=0 then 0 else sum(fpr.fpr_brl_amount)/sum(fpr.fpr_cal_base) end as UnitaryCost_Subscriber,

trim(to_char(fpr.fpr_batch_dttm,'Month')|| ' ' || to_char(fpr.fpr_batch_dttm,'yyyy')) as BatchMonth,

max(fpr.fpr_batch_dttm) as batchdttm_table1,

max(fpr.BTB_ID) as BTB_ID

from final_provisional_result fpr

join latest_prov_run_task_tab lt on fpr.pvr_id=lt.pvr_id

join bill_profile b on lt.bip_id=b.bip_id

join account a on a.acc_id=b.acc_id

join currency c on c.cur_id=fpr.bip_cur_id

group by a.acc_display,b.bip_name,c.cur_name, trim(to_char(fpr.fpr_batch_dttm,'Month')|| ' ' || to_char(fpr.fpr_batch_dttm,'yyyy')) order by batchdttm_table1;

Table2:

select a.acc_display as Partner

,c.cur_name as Currency

,sum(fvpr.fvr_brl_amount) as TotalCost_VOD,sum(fvpr.fvr_final_base) as PayingBase_VOD,

case when sum(fvpr.fvr_final_base)=0 then null else sum(fvpr.fvr_brl_amount)/sum(fvpr.fvr_final_base) end as UnitaryCost_VOD,

trim(to_char(bt.btb_to_dttm,'Month')|| ' ' || to_char(bt.btb_to_dttm,'yyyy')) as BatchMonth,

max(bt.btb_to_dttm) as batchdttm_table2,

max(bt.BTB_ID) as BTB_ID

from

account a join

bill_profile b on a.acc_id=b.acc_id

join latest_vodppv_view lt on lt.bip_id=b.bip_id

join final_vod_ppv_result fvpr on fvpr.bip_id=b.bip_id

join batch_tbl bt on bt.btb_id=fvpr.btb_id

join currency c on c.cur_id=fvpr.bip_cur_id

where fvpr.fvr_recording_type='VOD'

//where fvpr.fvr_type_offered='VOD'

group by a.acc_display,c.cur_name, trim(to_char(bt.btb_to_dttm,'Month')|| ' ' || to_char(bt.btb_to_dttm,'yyyy')) order by batchdttm_table2; 

Table3:

select a.acc_display as Partner

,c.cur_name as Currency

,sum(fvpr.fvr_brl_amount) as TotalCost_PPV,sum(fvpr.fvr_final_base) as PayingBase_PPV,

case when sum(fvpr.fvr_final_base)=0 then null else sum(fvpr.fvr_brl_amount)/sum(fvpr.fvr_final_base) end as UnitaryCost_PPV,

trim(to_char(bt.btb_to_dttm,'Month')|| ' ' || to_char(bt.btb_to_dttm,'yyyy')) as BatchMonth,

max(bt.btb_to_dttm) as batchdttm_table3,

max(bt.BTB_ID) as BTB_ID

from

account a join

bill_profile b on a.acc_id=b.acc_id

join latest_vodppv_view lt on lt.bip_id=b.bip_id

join final_vod_ppv_result fvpr on fvpr.bip_id=b.bip_id

join batch_tbl bt on bt.btb_id=fvpr.btb_id

join currency c on c.cur_id=fvpr.bip_cur_id

where fvpr.fvr_recording_type='PPV'

//where fvpr.fvr_type_offered='PPV'

group by a.acc_display,c.cur_name, trim(to_char(bt.btb_to_dttm,'Month')|| ' ' || to_char(bt.btb_to_dttm,'yyyy')) order by batchdttm_table3; 

I load these results into three separate charts.

Now I need to create  a  4th chart which will sum fields from 3 queries...

=(sum(TOTALCOST_VOD)+sum(TOTALCOST_PPV)+sum(TOTALCOST_SUBSCRIBER))/100000000// first exp

=(sum(UNITARYCOST_VOD)+sum(UNITARYCOST_PPV)+sum(UNITARYCOST_SUBSCRIBER))/100000000

the problem is 'BATCHMONTH' field will be union of batchmonth fields from all 3 queries.

In the 4th chart , the months are being displayed in a random order. How do I sort it?

I tried a couple of sort expressions, but none of them are working.

[Gysbert_Wassenaar · Accepted solution]

Try creating BatchMonth in a format that can be sorted properly:  to_char(fpr.fpr_batch_dttm,'YYYY MM') as BatchMonth,

If you still need BatchMonth in the form 'January 2012' the you could use last_day( fpr.fpr_batch_dttm ) to get the last day of the month as BatchMonth and do the formatting in the chart by setting it to 'MMMM YYYY' and set the sorting to numeric.

[#3]

Hi Gysbert,

Thanks a lot for taking the time out to reply.

The function last_day() doesn't seem to work..Its in red colour in the expression editor, which means

qlikview isn't recognising it. Could you help me out here, please?

I am using version 9.00.7778.9

Thanks and Regards,

Anju

[Gysbert_Wassenaar]

last_day() is an oracle sql function. You can use it in the sql statement. The qlikview equivalent is the monthend function.

[#5]

Hi Gysbert,

i am using this in my query..

to_char(fpr.fpr_batch_dttm,'YYYY MM') as BatchMonth,...

then, in qlikview..

i tried using a number of combinations in 'Calculated Dimension'

ex) month(BatchMonthTest)...Date()...etc...but  all of them returns null..

I think I should be performing formatting modifications on BatchMonth in qlikview  rather than SQL due to data association reasons?..

Also i tried using last_day()...In Oracle , the result is of format 31-JAN-12

but when I used the same field as dimension, it is being displayed as 1/31/2012 12:00:00 AM.

Kindly reply.

Regards,

Anju

[#6]

Hi,

I have one doubt,

max(last_day(fpr.fpr_batch_dttm)) as batchdttm_table1---if this is my sql field,

and if I am using monthname(batchdttm_table1) in qlikview, it gives me OCT 2012...is there any function which will give me October 2012?

Thanks and Regards,

Anju

[Gysbert_Wassenaar]

MonthName returns a date formatted as MMM YYYY, but it also has a numeric date value. MonthEnd is a date. Dates can be formatted in lots of ways. You can do that in the script or in the charts. See attached qvw for some examples. You also need to check out the date functions in the help file.

[#8]

Hi Gysbert,

My issue is resolved. Thanks a lot!

This is what I did..

I fetched this using query..

to_char(bt.btb_to_dttm,'YYYY MM') as BatchMonth,

max(last_day(bt.btb_to_dttm)) as BatchMonth_format

Then in the dimension field, I gave =date(BATCHMONTH_FORMAT,'MMMM YYYY')

and in the sort by tab, I gave BatchMonth..

Thanks and Regards,

Anju