topic Re: Script help in QlikView

Script help

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Script help

tresB — Fri, 27 Oct 2017 09:35:44 GMT

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;

Re: Script help

giakoum — Fri, 27 Oct 2017 09:36:02 GMT

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

Re: Script help

danielnevitt — Fri, 27 Oct 2017 10:19:19 GMT

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

Re: Script help

kicchu465 — Fri, 27 Oct 2017 10:36:07 GMT

Hi All,

Thanks a lot for your replies.

The above has achieved what I am looking for.

Regards,