Script help

[danielnevitt]

Hi,

I am trying to create a field that returns the following:

If min(final_pricing_dt) then Y else N - this should be split by udf_code

Example:

udf_code       period_cd               final_pricing_dt            required field

B                   Dec-17                    31/10/2017                  Y

B                   Jan-18                     30/11/2017                  N

B                   Feb-18                     28/12/2017                  N

B                   Mar-18                     31/01/2018                  N

B                   Dec-18                     28/02/2018                 N

ABF              Nov-17                     31/10/2017                 Y

ABF              Dec-17                     30/11/2017                 N

ABF              Jan-18                      21/12/2017                N

t would be really appreciated if someone could help with this.

Thanks,

Daniel

[tresesco]

Try like:

Input:

Load * Inline [

udf_code,       period_cd,               final_pricing_dt,            required field

B,                   Dec-17,                    31/10/2017,                  Y

B,                   Jan-18,                     30/11/2017,                  N

B,                   Feb-18,                     28/12/2017,                  N

B,                   Mar-18,                     31/01/2018,                  N

B,                   Dec-18,                     28/02/2018,                 N

ABF,              Nov-17,                     31/10/2017 ,                Y

ABF,              Dec-17,                     30/11/2017 ,                N

ABF,              Jan-18,                      21/12/2017,                N ] ;

Join

Load udf_code, Min(final_pricing_dt) as Maxd Resident Input Group By udf_code;

NoConcatenate

Final:

Load *, If (Maxd= final_pricing_dt, 'Y','N') as Flag Resident Input;

Drop Table Input;

[giakoum]

You need to create a separate table with minimum period_cd per udf_code and then left join it on the original table

[danielnevitt]

Hi,

Thank you very much for your detailed response.

I have attached my sql below.  Would you be able to advise where/how I should include the code you provided above?

select
tl.trader_initials,
tl.pos_lots,
BuySell=tl.bs_ind, --(case when bs_name = 'Buy' then 'B' else 'S' end),
tl.bs_name,
tl.pos_qty,
tl.trade_num,
tl.clearing_acct_cd,
tl.clearing_broker_cd,
sown.desk_cd,
tl.exchange_cd,
tl.final_pricing_dt,
tl.hdr_num,
sown.office_cd,
tl.period_cd,
tl.strategy_num,
str."reference" as strategy_reference,
tl.trade_dt,
tl.trade_type_group_cd,
tlu.udf_code,
ic.internal_co_legal_name,
tl.bfc_cd

from tempest_tier1.p_edw_trade_listed('%','%','%','%','%','%','Create Dt','2012-01-01','2050-01-01','%','Today','Trade','End Of Day',0) tl
join tempest_tier0.STRATEGY_OWNER as sown on (tl.strategy_num=sown.strategy_num)
join tempest_tier0.STRATEGY as str on(sown.strategy_num = str.strategy_num and sown.strategy_owner_num = 1)
left outer join tempest_tier1.v_edw_trade_listed_udf tlu on (tlu.pcr_num = tl.pcr_num)
left outer join tempest_tier1.v_edw_trade_udf tu on (tu.hdr_num = tl.hdr_num)
left outer join tempest_tier1.v_INTERNAL_COMPANY ic on (ic.internal_co_cd = tl.internal_co_cd)

where tl.final_pricing_dt > today() --'$(vDate)'
and tl.trade_status_ind not in ('C')
and tlu.udf_code in ('B')
and tl.exchange_cd in ('ICE');

LOAD [Commodity Derivative Name
(including associated contracts)],
[Venue MIC],
[Name of Trading Venue],
[Venue Product Codes] as udf_code,
[Principal Venue Product Code],
[Spot month single limit#],
[Other month limit#],
[Unit of measurement],
[Definition of spot month]
FROM

(ooxml, embedded labels, table is Sheet1);

Thanks,

Daniel

[kicchu465]

Hi All,

Thanks a lot for your replies.

The above has achieved what I am looking for.

Regards,

Kb