Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load statement - Field not found in SQL script

<body><p>Hi,</p> <p>I'm trying to run the following code but QV is returning the error:   Field not found - &lt;SettCcyAbbr&gt;</p> <p>Has anyone else experienced this problem/able to help please?</p> <p>Many thanks,</p> <p>Ronnie</p> <p> <p ><strong>Set</strong> </span>[QVDPath]</span> =</span>'K:\COMMON\</span><st1:stockticker>DIS</span></st1:stockticker>\1 Development &amp; Implementation\Qlikview\2. Qlikview Project - Phase 1\2. QVD Layer\1. Wave1 Developments\Triangulations\QVD'</span>;</span></span></p> <p ><strong>ODBC</strong> <strong>CONNECT</strong> <strong>TO</strong> </span>DBP0</span><st1:stockticker>DCS</span></st1:stockticker> </span>(XUserId</span> </span>is</span> </span>ZYHTbINIEaVATYAKRC,</span> </span>XPassword</span> </span>is</span> </span>SaUDUYRNRZaMXUVMXTGB);</span></span></p> <p ><strong>///$tab Q-Glob_Sett_Amts</strong></span></p> <p ><i>[Settlement]:</span></i></span></p> <p ><strong>Load</strong> </span></p> <p >SettCcyAbbr, </span></p> <p >LloydsRiskCode</span>, </span></p> <p >YearAccount</span>, </span></p> <p >SystemYear</span>, </span></p> <p >SystemQuarter</span>, </span></p> <p >Prem_Amnt</span>, </span></p> <p >[Prem _Amount_</span><st1:stockticker>GBP</span></st1:stockticker>]</span>, </span></p> <p >[Paid_Amnt]</span>, </span></p> <p >[Paid_Amnt_</span><st1:stockticker>GBP</span></st1:stockticker>]</span>, </span></p> <p >[OS]</span>, </span></p> <p >[OS_</span><st1:stockticker>GBP</span></st1:stockticker>];</span> </span></p> <p ><strong>SELECT</strong> </span></p> <p >AL6.Sett_Ccy_Abbr<strong> </strong></span>as SettCcyAbbr,<strong> </strong></span></p> <p >AL6.Lloyds_Risk_Code</span> </span>as</span> </span>LloydsRiskCode,</span> </span></p> <p >AL6.Year_Account</span> </span>as</span> </span>YearAccount,</span> </span></p> <p >AL6.Year</span> </span>as</span> </span>SystemYear,</span> </span></p> <p >AL6.Quarter</span> </span>as</span> </span>SystemQuarter,</span> </span></p> <p >-SUM(COALESCE(AL6.Premium_Amt,0))</span> </span>as</span> </span>PremAmnt,</span>  </span></span></p> <p >-SUM(COALESCE(float</span> </span>(</span> </span>AL6.Premium_Amt</span> </span>)</span> </span>/float</span> </span>(</span> </span>AL6.EX_</span><st1:stockticker>RATE</span></st1:stockticker>_TO_STRL</span> </span>)</span> </span>,0))</span> </span>as</span> </span>Prem_Amount_</span><st1:stockticker>GBP</span></st1:stockticker>,</span>  </span></span></p> <p >-SUM(COALESCE(AL6.Claim_Amt,0))</span> </span>as</span> </span>Paid_Amnt,</span>  </span></span></p> <p >-SUM(COALESCE(float</span> </span>(</span> </span>AL6.Claim_Amt</span> </span>)</span> </span>/</span> </span>float</span> </span>(</span> </span>AL6.EX_</span><st1:stockticker>RATE</span></st1:stockticker>_TO_STRL</span> </span>)</span> </span>,0))</span> </span>as</span> </span>Paid_Amnt_</span><st1:stockticker>GBP</span></st1:stockticker>,</span>  </span></span></p> <p >-sum(COALESCE(AL6.Os_Total_Orig,0))</span> </span>as</span> </span>OS,</span>  </span></span></p> <p >-SUM(COALESCE(float</span> </span>(</span> </span>AL6.Os_total_orig</span> </span>)</span> </span>/float</span> </span>(</span> </span>AL6.EX_</span><st1:stockticker>RATE</span></st1:stockticker>_TO_STRL</span> </span>)</span> </span>,0))</span> </span>as</span> </span>OS_</span><st1:stockticker>GBP</span></st1:stockticker> </span></p> <p ><strong>FROM</strong></span></p> <p >(SELECT</span>    </span></span>OUTER.Sett_Ccy_Abbr,</span></span></p> <p >OUTER.Lloyds_Risk_Code,</span></span></p> <p >OUTER.Year_Account,</span></span></p> <p >OUTER.Year,</span> </span></p> <p >OUTER.Quarter,</span></span></p> <p >DETAILS.Premium_Amt,</span></span></p> <p >DETAILS.Claim_Amt,</span></span></p> <p >DETAILS.OS_Total_Orig,</span></span></p> <p >DETAILS.ex_rate_to_strl</span></span></p> <p ><strong>From</strong></span></p> <p >(SELECT</span>    </span></span>AL1.Sett_Ccy_Abbr,</span></span></p> <p >AL1.Lloyds_Risk_Code,</span></span></p> <p >AL1.Year_Account,</span></span></p> <p >AL1.Quarter_No,</span></span></p> <p >AL1.System_Year,</span></span></p> <p >AL1.Premium_Amt,</span></span></p> <p >AL1.Claim_Amt,</span></span></p> <p >AL1.OS_Total_Orig,</span></span></p> <p >AL1.ex_rate_to_strl</span>  </span></span></p> <p ><strong>From</strong> </span><st1:stockticker>PEP</span></st1:stockticker>0MGR.TWAB90_GLOBAL_AGG</span> </span>AL1</span></span></p> <p >)</span> </span>as</span> </span>DETAILS</span></span></p> <p >RIGHT</span> </span>OUTER</span> </span>JOIN</span> </span></p> <p >(SELECT</span> </span>AL3.Year_Account,</span> </span></p> <p >AL5.Lloyds_Risk_Code,</span> </span></p> <p >AL5.Sett_Ccy_Abbr,</span></span></p> <p >AL4.Year,</span> </span></p> <p >AL4.Quarter</span> </span></p> <p ><strong>From</strong> </span><st1:stockticker>PEP</span></st1:stockticker>0MGR.TWAB94_GL_SR_</span><st1:stockticker>REF</span></st1:stockticker> </span>AL5,</span></span></p> <p ><st1:stockticker>PEP</span></st1:stockticker>0MGR.TWAB96_YOA_</span><st1:stockticker>REF</span></st1:stockticker>   </span></span>AL3,</span></span></p> <p ><st1:stockticker>PEP</span></st1:stockticker>0MGR.TWAB97_DATES_</span><st1:stockticker>REF</span></st1:stockticker> </span>AL4</span></span></p> <p >WHERE</span>      </span></span>AL3.Year_Account</span> </span>&lt;=</span>         </span></span>AL4.Year</span></span></p> <p ><st1:stockticker>AND</span></st1:stockticker>  </span></span>AL4.YEAR&gt;=1993</span></span></p> <p ><st1:stockticker>AND</span></st1:stockticker>  </span></span>AL3.YEAR_ACCOUNT&gt;=1993</span></span></p> <p ><st1:stockticker>AND</span></st1:stockticker>  </span></span>1=1</span></span></p> <p ><st1:stockticker>AND</span></st1:stockticker>  </span></span>1=1</span></span></p> <p ><st1:stockticker>AND</span></st1:stockticker>  </span></span>NOT</span> </span>EXISTS</span> </span></p> <p >(SELECT</span> </span>*</span> <strong>from</strong> </span><st1:stockticker>PEP</span></st1:stockticker>0MGR.TWAT76_RSK_EXCLUDE</span> </span></p> <p >where</span> </span>lloyds_risk_code</span>      </span></span>=</span> </span>AL5.lloyds_risk_code</span></span></p> <p ><st1:stockticker>AND</span></st1:stockticker>  </span></span>system_year</span> </span>&gt;</span> </span>AL3.year_account)</span></span></p> <p >)</span> </span>as</span> </span>OUTER</span>  </span></span></p> <p >ON</span>    </span></span>OUTER.Sett_Ccy_Abbr</span>        </span></span>=</span>       </span></span>DETAILS.Sett_Ccy_Abbr</span></span></p> <p ><st1:stockticker>AND</span></st1:stockticker> </span></span>OUTER.Lloyds_Risk_Code</span>   </span></span>=</span>       </span></span>DETAILS.Lloyds_Risk_Code</span> </span></p> <p ><st1:stockticker>AND</span></st1:stockticker> </span></span>OUTER.Year_Account</span>         </span></span>=</span>       </span></span>DETAILS.Year_Account</span> </span></p> <p ><st1:stockticker>AND</span></st1:stockticker> </span></span>OUTER.Quarter</span>           </span></span>=</span>       </span></span>DETAILS.Quarter_No</span> </span></p> <p ><st1:stockticker>AND</span></st1:stockticker> </span></span>OUTER.Year</span>                </span></span>=</span>       </span></span>DETAILS.System_Year</span></span></p> <p >)</span> </span>AS</span> </span>AL6</span></span></p> <p >Where</span> </span>1=1</span></span></p> <p ><st1:stockticker>AND</span></st1:stockticker>  </span></span>1=1</span></span></p> <p >GROUP</span> </span>BY</span></span></p> <p >AL6.Sett_Ccy_Abbr,</span></span></p> <p >AL6.LLOYDS_RISK_CODE,</span> </span></p> <p >AL6.YEAR_ACCOUNT,</span> </span></p> <p >AL6.YEAR,</span> </span></p> <p >AL6.QUARTER</span></span></p> <p >ORDER</span> </span>BY</span> </span></p> <p >AL6.Sett_Ccy_Abbr,</span></span></p> <p >AL6.LLOYDS_RISK_CODE,</span> </span></p> <p >AL6.YEAR_ACCOUNT,</span> </span></p> <p >AL6.YEAR,</span> </span></p> <p >AL6.QUARTER;</span></span></p> <p ><strong>Store</strong> </span>[Settlement]</span> <strong>into</strong> </span><i><strong>$(QVDPath)</strong></span></i>Triang_Settlement.qvd; </span></p> <p style="margin: 0cm 0cm 0pt;" > </span></p> <p style="margin: 0cm 0cm 0pt;" > </span></p> <p > </p> </span></p> <p > </p></body>

1 Reply
brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi,

the QV language works like this:

Tablename:
Load fieldname, fieldname2, ... fieldnamen;
SQL Select * From Table;

Then load another table and use join or concatenate