PB à toute heure et à tout moment. (à parcourir avec modération)
Le forum (ô combien francophone) des utilisateurs de Powerbuilder.
Annonce
Certaines rubriques, dont des cours, sont uniquement visibles par les membres du forum ^^.
Dans la rubrique Liens & Références, vous avez accès à un sommaire de téléchargement, profitez-en !
Il existe maintenant un nouveau TOPIC "Votre CV en Ligne" accessible uniquement par demande.
Dans la rubrique Liens & Références, vous avez accès à un sommaire de téléchargement, profitez-en !
Il existe maintenant un nouveau TOPIC "Votre CV en Ligne" accessible uniquement par demande.
#1 21-12-2011 13:59:16
- freewarefw
- Membre
- Date d'inscription: 09-09-2009
- Messages: 9
- Pépites: 38
- Banque: 0
[RESOLU] Total montant group 2 dans group 1 est doublé.
bonjour,
je travaille avec : windows XP sce pack 3,
Powerbuilder 7.03 build 10213
MS SQL 2000/SQL 2005.
j'ai une DW qui contient 2 groupes.
group 1 : pfolio
group 2 : devise (currency).
dans le group 2 j'ai un total par devise (currency)
ccy_swt_amount = If( Sum( det_ccy_balance for group 2 ) < 0 , Round( Sum( det_ccy_balance for group 2 ) * ccy_percent_swt / 100 , deal_ccy_cts ) , 0 )
dans le group 1 j'ai un total portefeuille qui regroupe le total de toutes les devices de ce portefeuille :
cpf_ccy_exposure = Sum( Round( ccy_swt_amount * If( view_type = 1, Round ( cb_deal_rate / cb_cst_rate , 10 ) ,
Round( lb_deal_rate / lb_cst_rate , 10 ) ) , cst_ccy_cts ) for group 1 )
le problème est que, dans le total cpf_ccy_exposure, le calcul fait me donne le double de ce que j'attends.
comment faire pour résoudre ce problème. sortir de la DW le calcul et le mettre dans Powerscript Retrieveend ?
qu'elle est la solution ? Je joins un export de cette DW.
Code: pb
$PBExportHeader$d_cpf_pla_ccy_e.srd $PBExportComments$Portfolio lending - Customer exposure by currency release 7; datawindow(units=0 timer_interval=0 color=281067712 processing=0 HTMLDW=no print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 97 print.margin.bottom = 97 print.paper.source = 0 print.paper.size = 0 print.prompt=no print.buttons=no print.preview.buttons=no ) summary(height=0 color="536870912" ) footer(height=0 color="536870912" ) detail(height=172 color="536870912" ) table(column=(type=long updatewhereclause=yes name=customer dbname="customer" ) column=(type=long updatewhereclause=yes name=pfolio dbname="pfolio" ) column=(type=long updatewhereclause=yes name=val_type dbname="val_type" ) column=(type=char(4) updatewhereclause=yes name=atp_code dbname="atp_code" ) column=(type=long updatewhereclause=yes name=atp_attach1 dbname="atp_attach1" ) column=(type=long updatewhereclause=yes name=atp_attach2 dbname="atp_attach2" ) column=(type=char(3) updatewhereclause=yes name=ccy_code dbname="ccy_code" ) column=(type=char(15) updatewhereclause=yes name=ccy_desc dbname="ccy_desc" ) column=(type=long updatewhereclause=yes name=ccy_swt_exp dbname="ccy_swt_exp" ) column=(type=long updatewhereclause=yes name=ccy_swt_pos dbname="ccy_swt_pos" ) column=(type=char(1) updatewhereclause=yes name=ccy_swt_cumul dbname="ccy_swt_cumul" ) column=(type=decimal(4) updatewhereclause=yes name=cb_balance dbname="cb_balance" ) column=(type=decimal(4) updatewhereclause=yes name=lb_balance dbname="lb_balance" ) column=(type=decimal(8) updatewhereclause=yes name=sec_mk_cb_price dbname="sec_mk_cb_price" ) column=(type=decimal(8) updatewhereclause=yes name=sec_mk_lb_price dbname="sec_mk_lb_price" ) column=(type=decimal(6) updatewhereclause=yes name=sec_mk_cb_price_nom dbname="sec_mk_cb_price_nom" ) column=(type=decimal(6) updatewhereclause=yes name=sec_mk_lb_price_nom dbname="sec_mk_lb_price_nom" ) column=(type=char(3) updatewhereclause=yes name=sec_mk_cb_price_ccy dbname="sec_mk_cb_price_ccy" ) column=(type=char(3) updatewhereclause=yes name=sec_mk_lb_price_ccy dbname="sec_mk_lb_price_ccy" ) column=(type=decimal(10) updatewhereclause=yes name=sec_mk_cb_price_rate dbname="sec_mk_cb_price_rate" ) column=(type=decimal(10) updatewhereclause=yes name=sec_mk_lb_price_rate dbname="sec_mk_lb_price_rate" ) column=(type=long updatewhereclause=yes name=sec_mk_cb_price_ccy_cts dbname="sec_mk_cb_price_ccy_cts" ) column=(type=long updatewhereclause=yes name=sec_mk_lb_price_ccy_cts dbname="sec_mk_lb_price_ccy_cts" ) column=(type=decimal(8) updatewhereclause=yes name=sec_pp_cb_price dbname="sec_pp_cb_price" ) column=(type=decimal(8) updatewhereclause=yes name=sec_pp_lb_price dbname="sec_pp_lb_price" ) column=(type=decimal(6) updatewhereclause=yes name=sec_pp_price_nom dbname="sec_pp_price_nom" ) column=(type=decimal(8) updatewhereclause=yes name=int_cr_rate dbname="int_cr_rate" ) column=(type=decimal(8) updatewhereclause=yes name=int_db_rate dbname="int_db_rate" ) column=(type=long updatewhereclause=yes name=int_cr_basis dbname="int_cr_basis" ) column=(type=long updatewhereclause=yes name=int_db_basis dbname="int_db_basis" ) column=(type=decimal(4) updatewhereclause=yes name=int_cr_amount dbname="int_cr_amount" ) column=(type=decimal(4) updatewhereclause=yes name=int_db_amount dbname="int_db_amount" ) column=(type=decimal(4) updatewhereclause=yes name=int_bv_amount dbname="int_bv_amount" ) column=(type=long updatewhereclause=yes name=int_exday dbname="int_exday" ) column=(type=datetime updatewhereclause=yes name=start_date dbname="start_date" ) column=(type=datetime updatewhereclause=yes name=int_date dbname="int_date" ) column=(type=datetime updatewhereclause=yes name=end_date dbname="end_date" ) column=(type=datetime updatewhereclause=yes name=mat_date dbname="mat_date" ) column=(type=char(3) updatewhereclause=yes name=pfs_code dbname="pfs_code" ) column=(type=char(30) updatewhereclause=yes name=pfs_desc dbname="pfs_desc" ) column=(type=decimal(10) updatewhereclause=yes name=cb_deal_rate dbname="cb_deal_rate" ) column=(type=decimal(10) updatewhereclause=yes name=lb_deal_rate dbname="lb_deal_rate" ) column=(type=long updatewhereclause=yes name=deal_ccy_cts dbname="deal_ccy_cts" ) column=(type=char(3) updatewhereclause=yes name=cst_ccy dbname="cst_ccy" ) column=(type=decimal(10) updatewhereclause=yes name=cb_cst_rate dbname="cb_cst_rate" ) column=(type=decimal(10) updatewhereclause=yes name=lb_cst_rate dbname="lb_cst_rate" ) column=(type=long updatewhereclause=yes name=cst_ccy_cts dbname="cst_ccy_cts" ) column=(type=datetime updatewhereclause=yes name=cb_line_date dbname="cb_line_date" ) column=(type=datetime updatewhereclause=yes name=lb_line_date dbname="lb_line_date" ) column=(type=decimal(8) updatewhereclause=yes name=mk_vd_pp_aver dbname="mk_vd_pp_aver" ) column=(type=decimal(8) updatewhereclause=yes name=mk_td_pp_aver dbname="mk_td_pp_aver" ) column=(type=long updatewhereclause=yes name=view_type dbname="view_type" ) retrieve="SELECT customer, pfolio, val_type, atp_code, atp_attach1, atp_attach2, ccy_code, ccy_desc, ccy_swt_exp, ccy_swt_pos, ccy_swt_cumul, cb_balance, lb_balance, sec_mk_cb_price, sec_mk_lb_price, sec_mk_cb_price_nom, sec_mk_lb_price_nom, sec_mk_cb_price_ccy, sec_mk_lb_price_ccy, sec_mk_cb_price_rate, sec_mk_lb_price_rate, sec_mk_cb_price_ccy_cts, sec_mk_lb_price_ccy_cts, sec_pp_cb_price, sec_pp_lb_price, sec_pp_price_nom, int_cr_rate, int_db_rate, int_cr_basis, int_db_basis, int_cr_amount, int_db_amount, int_bv_amount, int_exday, start_date, int_date, end_date, mat_date, pfs_code, pfs_desc, cb_deal_rate, lb_deal_rate, deal_ccy_cts, cst_ccy, cb_cst_rate, lb_cst_rate, cst_ccy_cts, cb_line_date, lb_line_date, mk_vd_pp_aver, mk_td_pp_aver, :view_type view_type FROM customer_extract WHERE customer = :customer AND rel_cst = :customer AND ( pfolio= :pfolio OR :pfolio = 99)" arguments=(("customer", number),("pfolio", number),("view_type", number)) sort="pfolio A ccy_code A atp_code A pfs_code A " ) group(level=1 header.height=140 trailer.height=336 by=("pfolio" ) header.color="536870912" trailer.color="536870912" ) group(level=2 header.height=64 trailer.height=228 by=("ccy_code" ) header.color="536870912" trailer.color="536870912" ) compute(band=header.1 alignment="0" expression="~" Lending for portfolio no. ~" + String( pfolio )"border="0" color="0" x="32" y="8" height="52" width="1637" format="[GENERAL]" name=h_pfolio font.face="MS Sans Serif" font.height="-8" font.weight="700" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) text(band=header.1 alignment="2" text="Currency " border="0" color="0" x="41" y="76" height="52" width="297" name=t_1 font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) text(band=header.1 alignment="2" text="Assets" border="0" color="0" x="1042" y="76" height="52" width="206" name=t_2 font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) text(band=header.1 alignment="2" text="Liabilities" border="0" color="0" x="1527" y="76" height="52" width="256" name=t_3 font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) text(band=header.1 alignment="2" text="Exposure" border="0" color="255" x="2066" y="76" height="52" width="261" name=t_4 font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) text(band=header.1 alignment="2" text="---- Weigthing ----" border="0" color="0" x="2405" y="76" height="52" width="480" name=t_5 font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) line(band=header.1 x1="0" y1="132" x2="5001" y2="132" name=l_1 pen.style="0" pen.width="5" pen.color="0" background.mode="1" background.color="553648127" ) column(band=header.2 id=8 alignment="0" tabsequence=32766 border="0" color="0" x="27" y="4" height="52" width="722" format="[general]" name=ccy_desc edit.limit=0 edit.case=any edit.autoselect=yes font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=header.2 alignment="1" expression="Sum( det_asset for group 2 )"border="0" color="0" x="768" y="4" height="52" width="498" format="#,##0.00##;[RED]#,##0.00##" name=ccy_asset font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=header.2 alignment="1" expression="Sum( det_liability for group 2 )"border="0" color="0" x="1289" y="4" height="52" width="498" format="#,##0.00##;[RED]#,##0.00##" name=ccy_liability font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=header.2 alignment="1" expression="If ( Sum( det_asset + det_liability for group 2 ) < 0 , Sum( det_asset + det_liability for group 2 ) , 0 )"border="0" color="0" x="1833" y="4" height="52" width="498" format="#,##0.00##;[RED]#,##0.00##" name=ccy_exposure font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=header.2 alignment="1" expression=" det_swt_percent "border="0" color="0" x="2391" y="4" height="56" width="183" format="##0 '%'" name=ccy_percent_swt font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="281067712" ) compute(band=header.2 alignment="1" expression="If( Sum( det_ccy_balance for group 2 ) < 0 , Round( Sum( det_ccy_balance for group 2 ) * ccy_percent_swt / 100 , deal_ccy_cts ) , 0 ) "border="0" color="0" x="2615" y="4" height="52" width="311" format="#,##0.00##;[RED]#,##0.00##" name=ccy_swt_amount font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="65535" ) compute(band=detail alignment="1" expression="If( det_ccy_balance < 0 , det_ccy_balance, 0 )"border="0" color="0" x="1289" y="4" height="52" width="498" format="#,##0.00##;[RED]#,##0.00##" name=det_liability font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=detail alignment="1" expression="If( det_ccy_balance > 0 , det_ccy_balance, 0 )"border="0" color="0" x="768" y="4" height="52" width="498" format="#,##0.00##;[RED]#,##0.00##" name=det_asset font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=detail alignment="0" expression="If( view_type = 1 , case ( atp_attach1 when 11 then Round( cb_balance * ( sec_pp_cb_price / sec_pp_price_nom ) , deal_ccy_cts ) when 12 then Round ( mk_vd_pp_aver, deal_ccy_cts) else cb_balance ) , case ( atp_attach1 when 11 then Round( lb_balance * ( sec_pp_lb_price / sec_pp_price_nom ) , deal_ccy_cts ) when 12 then Round ( mk_td_pp_aver, deal_ccy_cts) else lb_balance ) )"border="0" color="0" x="1838" y="4" height="52" width="498" format="[GENERAL]" name=det_ccy_balance font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=detail alignment="1" expression="If( ccy_code <> cst_ccy , ccy_swt_exp , 0 ) "border="0" color="0" x="2395" y="4" height="52" width="183" format="##0 '%'" name=det_swt_percent font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=detail alignment="0" expression="If( det_ccy_balance < 0 , Round( det_ccy_balance * det_swt_percent / 100 , deal_ccy_cts ) , 0 )"border="0" color="0" x="2615" y="4" height="52" width="251" format="[GENERAL]" name=det_swt_amount font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=trailer.2 alignment="1" expression="Round ( det_ccy_balance * If( view_type = 1, Round ( cb_deal_rate / cb_cst_rate , 10 ) , Round( lb_deal_rate / lb_cst_rate , 10 ) ) , cst_ccy_cts ) "border="0" color="0" x="3168" y="20" height="52" width="366" format="#,##0.00##;[RED]#,##0.00##" name=det_cv_balance font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) column(band=trailer.2 id=9 alignment="0" tabsequence=32766 border="0" color="0" x="5015" y="28" height="56" width="183" format="[general]" name=ccy_swt_exp edit.limit=0 edit.case=any edit.autoselect=yes font.face="Arial" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="16777215" ) column(band=trailer.2 id=10 alignment="0" tabsequence=32766 border="0" color="0" x="5266" y="28" height="56" width="183" format="[general]" name=ccy_swt_pos edit.limit=0 edit.case=any edit.autoselect=yes font.face="Arial" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="16777215" ) column(band=trailer.2 id=50 alignment="0" tabsequence=32766 border="0" color="0" x="3881" y="0" height="52" width="2002" format="[general]" name=mk_vd_pp_aver edit.limit=0 edit.case=any edit.autoselect=yes font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) column(band=trailer.2 id=51 alignment="0" tabsequence=32766 border="0" color="0" x="6386" y="40" height="52" width="562" format="[general]" name=mk_td_pp_aver edit.limit=0 edit.case=any edit.autoselect=yes font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) compute(band=trailer.1 alignment="0" expression="Sum( Round( ccy_swt_amount * If( view_type = 1, Round ( cb_deal_rate / cb_cst_rate , 10 ) , Round( lb_deal_rate / lb_cst_rate , 10 ) ) , cst_ccy_cts ) for group 1 )"border="0" color="0" x="1742" y="12" height="52" width="453" format="#,##0.00##;[RED]#,##0.00##" name=cpf_ccy_exposure font.face="MS Sans Serif" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="65535" ) compute(band=trailer.1 alignment="1" expression="~"Total exposure pfolio no. ~" + String( pfolio) + ~" in ~" + cst_ccy + ~" : ~" "border="0" color="0" x="128" y="12" height="52" width="1595" format="[GENERAL]" name=compute_1 font.face="MS Sans Serif" font.height="-8" font.weight="700" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="553648127" ) htmltable(border="0" ) htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" )
je vous remercie par avance de vos réponses.
cordialement
Dernière modification par freewarefw (06-02-2012 07:46:53)
Hors ligne
#2 30-01-2012 15:57:18
- Amélie
- Membre Geek
- Date d'inscription: 24-11-2009
- Messages: 24
- Pépites: 135
- Banque: 0
Re: [RESOLU] Total montant group 2 dans group 1 est doublé.
Bonjour
j'ai eu le même soucis et j'ai donc demandé à Sybase si cela était normal.
En fait cpf_ccy_exposure ne double pas obligatoirement le résultat mais il prend la valeur de ccy_swt_amount qu'il somme autant de fois qu'il y a de ligne dans le détail du group 2.
Par exemple :
J'ai 5 lignes de détail avec un champ val qui vaut 1 à chaque fois avec 2 groupes( Main d'oeuvre et type de travail)
Pour ma main d'oeuvre, j'ai 2 type de travail A et B. Pour A, j'ai 3 lignes de détail et pour B j'ai 2 lignes de détail
Je crée 2 computes :
Code:
sum_type_travail = sum(val for group 2) sum_main_oeuvre = sum(sum_type_travail for group 1)
Résultat :
Pour A, sum_type_travail = 3 et Pour B sum_type_travail = 2
sum_main_oeuvre = 13 car PB fait sum_type_travail de A * nb ligne de A ( 3*3) + sum_type_travail de B * nb ligne de B ( 2*2)
Dernière modification par Amélie (30-01-2012 16:03:48)
Hors ligne
