topic Re: Date Format in QlikView

Date Format

danielnevitt — Tue, 02 Feb 2016 13:03:47 GMT

Hi,

I have the below sql and I would like to specify the date for field aps.bl_dt as dd/mm/yyyy. Currently it returns format dd/mm/yyyy hh:mm:ss.

Any helps will be much appreciated.

Regards,

Daniel

SELECT aps.bl_dt, aps.title_transfer_dt, aps.strategy_num, aps.pcr_num, aps.trade_num, aps.modify_dt, aps.title_transfer_dt_ind, aps.delete_dt, aps.delete_initials, aps.actual_qty/100 actual_qty, aps.actual_qty_uom_cd, aps.api_gravity, aps.specific_gravity,
so.desk_cd, so.office_cd,
CASE ph.trade_status_ind
WHEN 'B' THEN 'Partially Cancelled'
WHEN 'C' THEN 'Cancelled'
WHEN 'E' THEN 'Partially Finalized'
WHEN 'F' THEN 'Finalized'
WHEN 'L' THEN 'PL Run'
WHEN 'N' THEN 'New Trade'
WHEN 'P' THEN 'Parceled'
WHEN 'Q' THEN 'Partially Realized'
WHEN 'R' THEN 'Realized'
WHEN 'V' THEN 'Vouchered'
ELSE 'Other'
END 'trade_status_ind',
c.co_cd

FROM tempest_rpt_prod.dbo.AUDIT_PHYSICAL_SETTLEMENT aps
left outer join tempest_rpt_prod.dbo.STRATEGY_OWNER so on (so.strategy_num = aps.strategy_num)
left outer join tempest_rpt_prod.dbo.PHYSICAL_HDR ph on (ph.hdr_num = aps.hdr_num)
left outer join tempest_rpt_prod.dbo.CONTRACT c on (c.contract_num = ph.contract_num)
where aps.modify_dt>= '2016-01-01'
and so.office_cd in ('Non-US')

Re: Date Format

sunny_talwar — Tue, 02 Feb 2016 13:06:49 GMT

May be fix it in the SQL itself using SQL functions or use preceding load in QlikView to fix it:

LOAD *,

Date(Floor(aps.bl_dt), 'DD/MM/YYYY') as DateField;

SELECT aps.bl_dt, aps.title_transfer_dt, aps.strategy_num, aps.pcr_num, aps.trade_num, aps.modify_dt, aps.title_transfer_dt_ind, aps.delete_dt, aps.delete_initials, aps.actual_qty/100 actual_qty, aps.actual_qty_uom_cd, aps.api_gravity, aps.specific_gravity,

so.desk_cd, so.office_cd,

CASE ph.trade_status_ind

WHEN 'B' THEN 'Partially Cancelled'

WHEN 'C' THEN 'Cancelled'

WHEN 'E' THEN 'Partially Finalized'

WHEN 'F' THEN 'Finalized'

WHEN 'L' THEN 'PL Run'

WHEN 'N' THEN 'New Trade'

WHEN 'P' THEN 'Parceled'

WHEN 'Q' THEN 'Partially Realized'

WHEN 'R' THEN 'Realized'

WHEN 'V' THEN 'Vouchered'

ELSE 'Other'

END 'trade_status_ind',

c.co_cd

FROM tempest_rpt_prod.dbo.AUDIT_PHYSICAL_SETTLEMENT aps

left outer join tempest_rpt_prod.dbo.STRATEGY_OWNER so on (so.strategy_num = aps.strategy_num)

left outer join tempest_rpt_prod.dbo.PHYSICAL_HDR ph on (ph.hdr_num = aps.hdr_num)

left outer join tempest_rpt_prod.dbo.CONTRACT c on (c.contract_num = ph.contract_num)

where aps.modify_dt>= '2016-01-01'

and so.office_cd in ('Non-US');

Re: Date Format

danielnevitt — Tue, 02 Feb 2016 13:08:36 GMT

Thanks Sunny.

Ideally I would like to fix using the SQL functions (if possible).

Regards,

Daniel

Re: Date Format

sunny_talwar — Tue, 02 Feb 2016 13:10:30 GMT

In that case, you can use TRUNC() function in SQL