topic Re: seq_no in QlikView https://community.qlik.com/t5/QlikView/seq-no/m-p/1096643#M364269 <HTML><HEAD></HEAD><BODY><P>Thanks Sunny</P><P></P><P>It is working with a bit of problem my output is as follows</P><TABLE><TBODY><TR><TD>C</TD><TD>YEAR</TD><TD>PERIOD_FROM</TD><TD>PERIOD_TO</TD><TD>NEW_REN</TD><TD>LAST_REN_YEAR</TD><TD>SEQ_NO</TD></TR><TR><TD>NI00111C000039</TD><TD>2012</TD><TD>10-Feb-2012</TD><TD>09-Feb-2013</TD><TD>REN</TD><TD>2014</TD><TD>3</TD></TR><TR><TD>NI00111C000039</TD><TD>2013</TD><TD>10-Feb-2013</TD><TD>09-Feb-2014</TD><TD>REN</TD><TD>2014</TD><TD>2</TD></TR><TR><TD>NI00111C000039</TD><TD>2014</TD><TD>10-Feb-2014</TD><TD>09-Feb-2015</TD><TD>REN</TD><TD>2014</TD><TD>1</TD></TR><TR><TD>NI00121F000014</TD><TD>2012</TD><TD>06-Mar-2012</TD><TD>05-Mar-2013</TD><TD>NEW</TD><TD>2015</TD><TD>4</TD></TR><TR><TD>NI00121F000014</TD><TD>2013</TD><TD>06-Mar-2013</TD><TD>05-Mar-2014</TD><TD>REN</TD><TD>2015</TD><TD>3</TD></TR><TR><TD>NI00121F000014</TD><TD>2014</TD><TD>06-Mar-2014</TD><TD>05-Mar-2015</TD><TD>REN</TD><TD>2015</TD><TD>2</TD></TR><TR><TD>NI00121F000014</TD><TD>2015</TD><TD>06-Mar-2015</TD><TD>05-Mar-2016</TD><TD>REN</TD><TD>2015</TD><TD>1</TD></TR><TR><TD>NI00144F0000098</TD><TD>2014</TD><TD>27-Sep-2014</TD><TD>26-Sep-2015</TD><TD>NEW</TD><TD>2015</TD><TD>2</TD></TR><TR><TD>NI00144F0000098</TD><TD>2015</TD><TD>05-Oct-2015</TD><TD>04-Oct-2016</TD><TD>REN</TD><TD>2015</TD><TD>1</TD></TR><TR><TD>NI00141H0000032</TD><TD>2014</TD><TD>10-Nov-2014</TD><TD>09-Nov-2015</TD><TD>NEW</TD><TD>2015</TD><TD>2</TD></TR><TR><TD>NI00141H0000032</TD><TD>2015</TD><TD>10-Nov-2015</TD><TD>09-Nov-2016</TD><TD>REN</TD><TD>2015</TD><TD>1</TD></TR></TBODY></TABLE><P></P><P>Seq No is in descending order.</P><P>My script is as follows</P><P></P><P>LOAD</P><P>BCOD,</P><P>CLA_CODE,</P><P>CLAIM_NO,</P><P>CUS_TYPE,</P><P>LOSS_DATE,</P><P>NEW_REN,</P><P>PAID_AMOUNT,</P><P>PERIOD_FROM,</P><P>PERIOD_TO,</P><P>POLICY_NO,</P><P>PRD_CODE,</P><P>PREMIUM,</P><P>REN,</P><P>YEAR,</P><P>If(POLICY_NO = Peek('POLICY_NO'), Peek('LAST_REN_YEAR'), LAST_REN_YEAR) as LAST_REN_YEAR,</P><P> If(POLICY_NO = Peek('POLICY_NO'), RangeSum(Peek('SEQ_NO'), 1), 1) as SEQ_NO</P><P>Resident FINAL_DATA</P><P>Order By POLICY_NO,PERIOD_FROM desc;</P><P></P><P>Kindly correct me</P><P></P><P>Pls also let me know how this rangesum function works </P></BODY></HTML> Sat, 05 Mar 2016 03:25:37 GMT upaliwije 2016-03-05T03:25:37Z seq_no https://community.qlik.com/t5/QlikView/seq-no/m-p/1096641#M364267 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>I have following data(POLICY_NO AND PERIOD_FROM columns)&nbsp; in my document I want to create a third column of data with SEQ_NO based on the YEAR(<SPAN style="font-size: 13.3333px;">PERIOD_FROM )&nbsp; for each POLICY_NO in data load script&nbsp; as I have shown in my sample in red</SPAN></P><P></P><P>&nbsp;&nbsp;&nbsp; </P><TABLE border="0" cellpadding="0" cellspacing="0" style="width: 283px;"><TBODY><TR><TD class="xl63" height="17" width="121">POLICY_NO</TD><TD class="xl63" style="border-left: none;" width="98">PERIOD_FROM</TD><TD width="64"><SPAN style="color: #ff0000;"> SEQ_NO</SPAN></TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00111C000039</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">10-Feb-2012</TD><TD align="right"><SPAN style="color: #ff0000;">1</SPAN></TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00111C000039</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">10-Feb-2013</TD><TD align="right"><SPAN style="color: #ff0000;">2</SPAN></TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00111C000039</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">10-Feb-2014</TD><TD align="right"><SPAN style="color: #ff0000;">3</SPAN></TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00121F000014</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">06-Mar-2012</TD><TD align="right">1</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00121F000014</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">06-Mar-2013</TD><TD align="right">2</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00121F000014</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">06-Mar-2014</TD><TD align="right">3</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00121F000014</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">06-Mar-2015</TD><TD align="right">4</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00144F0000098</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">27-Sep-2014</TD><TD align="right">1</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00144F0000098</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">05-Oct-2015</TD><TD align="right">2</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00121C000067</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">18-May-2012</TD><TD align="right">1</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00121C000067</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">18-May-2013</TD><TD align="right">2</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00121C000067</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">18-May-2014</TD><TD align="right">3</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">NI00121C000067</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">18-May-2015</TD><TD align="right">4</TD></TR></TBODY></TABLE><P></P><P></P><P>Pls help me with script</P></BODY></HTML> Sat, 05 Mar 2016 02:02:37 GMT https://community.qlik.com/t5/QlikView/seq-no/m-p/1096641#M364267 upaliwije 2016-03-05T02:02:37Z Re: seq_no https://community.qlik.com/t5/QlikView/seq-no/m-p/1096642#M364268 <HTML><HEAD></HEAD><BODY><P>Try this:</P><P></P><P><STRONG>Table:</STRONG></P><P><STRONG>LOAD * Inline [</STRONG></P><P><STRONG>POLICY_NO, PERIOD_FROM</STRONG></P><P><STRONG>NI00111C000039, 10-Feb-2012</STRONG></P><P><STRONG>NI00111C000039, 10-Feb-2013</STRONG></P><P><STRONG>NI00111C000039, 10-Feb-2014</STRONG></P><P><STRONG>NI00121F000014, 06-Mar-2012</STRONG></P><P><STRONG>NI00121F000014, 06-Mar-2013</STRONG></P><P><STRONG>NI00121F000014, 06-Mar-2014</STRONG></P><P><STRONG>NI00121F000014, 06-Mar-2015</STRONG></P><P><STRONG>NI00144F0000098, 27-Sep-2014</STRONG></P><P><STRONG>NI00144F0000098, 05-Oct-2015</STRONG></P><P><STRONG>NI00121C000067, 18-May-2012</STRONG></P><P><STRONG>NI00121C000067, 18-May-2013</STRONG></P><P><STRONG>NI00121C000067, 18-May-2014</STRONG></P><P><STRONG>NI00121C000067, 18-May-2015</STRONG></P><P><STRONG>];</STRONG></P><P></P><P><STRONG>FinalTable:</STRONG></P><P><STRONG>LOAD *,</STRONG></P><P><STRONG>&nbsp; If(POLICY_NO = Peek('POLICY_NO'), RangeSum(Peek('SEQ_NO'), 1), 1) as SEQ_NO</STRONG></P><P><STRONG>Resident Table</STRONG></P><P><STRONG>Order By POLICY_NO, PERIOD_FROM;</STRONG></P><P></P><P><STRONG>DROP Table Table;</STRONG></P><P><STRONG><BR /></STRONG></P><P><STRONG><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/117060_Capture.PNG" style="height: auto;" /><BR /></STRONG></P></BODY></HTML> Sat, 05 Mar 2016 03:12:32 GMT https://community.qlik.com/t5/QlikView/seq-no/m-p/1096642#M364268 sunny_talwar 2016-03-05T03:12:32Z Re: seq_no https://community.qlik.com/t5/QlikView/seq-no/m-p/1096643#M364269 <HTML><HEAD></HEAD><BODY><P>Thanks Sunny</P><P></P><P>It is working with a bit of problem my output is as follows</P><TABLE><TBODY><TR><TD>C</TD><TD>YEAR</TD><TD>PERIOD_FROM</TD><TD>PERIOD_TO</TD><TD>NEW_REN</TD><TD>LAST_REN_YEAR</TD><TD>SEQ_NO</TD></TR><TR><TD>NI00111C000039</TD><TD>2012</TD><TD>10-Feb-2012</TD><TD>09-Feb-2013</TD><TD>REN</TD><TD>2014</TD><TD>3</TD></TR><TR><TD>NI00111C000039</TD><TD>2013</TD><TD>10-Feb-2013</TD><TD>09-Feb-2014</TD><TD>REN</TD><TD>2014</TD><TD>2</TD></TR><TR><TD>NI00111C000039</TD><TD>2014</TD><TD>10-Feb-2014</TD><TD>09-Feb-2015</TD><TD>REN</TD><TD>2014</TD><TD>1</TD></TR><TR><TD>NI00121F000014</TD><TD>2012</TD><TD>06-Mar-2012</TD><TD>05-Mar-2013</TD><TD>NEW</TD><TD>2015</TD><TD>4</TD></TR><TR><TD>NI00121F000014</TD><TD>2013</TD><TD>06-Mar-2013</TD><TD>05-Mar-2014</TD><TD>REN</TD><TD>2015</TD><TD>3</TD></TR><TR><TD>NI00121F000014</TD><TD>2014</TD><TD>06-Mar-2014</TD><TD>05-Mar-2015</TD><TD>REN</TD><TD>2015</TD><TD>2</TD></TR><TR><TD>NI00121F000014</TD><TD>2015</TD><TD>06-Mar-2015</TD><TD>05-Mar-2016</TD><TD>REN</TD><TD>2015</TD><TD>1</TD></TR><TR><TD>NI00144F0000098</TD><TD>2014</TD><TD>27-Sep-2014</TD><TD>26-Sep-2015</TD><TD>NEW</TD><TD>2015</TD><TD>2</TD></TR><TR><TD>NI00144F0000098</TD><TD>2015</TD><TD>05-Oct-2015</TD><TD>04-Oct-2016</TD><TD>REN</TD><TD>2015</TD><TD>1</TD></TR><TR><TD>NI00141H0000032</TD><TD>2014</TD><TD>10-Nov-2014</TD><TD>09-Nov-2015</TD><TD>NEW</TD><TD>2015</TD><TD>2</TD></TR><TR><TD>NI00141H0000032</TD><TD>2015</TD><TD>10-Nov-2015</TD><TD>09-Nov-2016</TD><TD>REN</TD><TD>2015</TD><TD>1</TD></TR></TBODY></TABLE><P></P><P>Seq No is in descending order.</P><P>My script is as follows</P><P></P><P>LOAD</P><P>BCOD,</P><P>CLA_CODE,</P><P>CLAIM_NO,</P><P>CUS_TYPE,</P><P>LOSS_DATE,</P><P>NEW_REN,</P><P>PAID_AMOUNT,</P><P>PERIOD_FROM,</P><P>PERIOD_TO,</P><P>POLICY_NO,</P><P>PRD_CODE,</P><P>PREMIUM,</P><P>REN,</P><P>YEAR,</P><P>If(POLICY_NO = Peek('POLICY_NO'), Peek('LAST_REN_YEAR'), LAST_REN_YEAR) as LAST_REN_YEAR,</P><P> If(POLICY_NO = Peek('POLICY_NO'), RangeSum(Peek('SEQ_NO'), 1), 1) as SEQ_NO</P><P>Resident FINAL_DATA</P><P>Order By POLICY_NO,PERIOD_FROM desc;</P><P></P><P>Kindly correct me</P><P></P><P>Pls also let me know how this rangesum function works </P></BODY></HTML> Sat, 05 Mar 2016 03:25:37 GMT https://community.qlik.com/t5/QlikView/seq-no/m-p/1096643#M364269 upaliwije 2016-03-05T03:25:37Z Re: seq_no https://community.qlik.com/t5/QlikView/seq-no/m-p/1096644#M364270 <HTML><HEAD></HEAD><BODY><P>You need ascending order? Try this:</P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>LOAD</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>BCOD,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>CLA_CODE,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>CLAIM_NO,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>CUS_TYPE,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>LOSS_DATE,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>NEW_REN,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>PAID_AMOUNT,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>PERIOD_FROM,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>PERIOD_TO,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>POLICY_NO,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>PRD_CODE,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>PREMIUM,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>REN,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>YEAR,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>If(POLICY_NO = Peek('POLICY_NO'), Peek('LAST_REN_YEAR'), LAST_REN_YEAR) as LAST_REN_YEAR,</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>If(POLICY_NO = Peek('POLICY_NO'), RangeSum(Peek('SEQ_NO'), 1), 1) as SEQ_NO</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>Resident FINAL_DATA</STRONG></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>Order By POLICY_NO, PERIOD_FROM <SPAN style="color: #ff0000;">asc</SPAN>;</STRONG></P></BODY></HTML> Sat, 05 Mar 2016 03:33:49 GMT https://community.qlik.com/t5/QlikView/seq-no/m-p/1096644#M364270 sunny_talwar 2016-03-05T03:33:49Z Re: seq_no https://community.qlik.com/t5/QlikView/seq-no/m-p/1096645#M364271 <HTML><HEAD></HEAD><BODY><P><EM>Table:</EM></P><P><EM>LOAD * Inline [</EM></P><P><EM>POLICY_NO, PERIOD_FROM</EM></P><P><EM>NI00111C000039, 10-Feb-2012</EM></P><P><EM>NI00111C000039, 10-Feb-2013</EM></P><P><EM>NI00111C000039, 10-Feb-2014</EM></P><P><EM>NI00121F000014, 06-Mar-2012</EM></P><P><EM>NI00121F000014, 06-Mar-2013</EM></P><P><EM>NI00121F000014, 06-Mar-2014</EM></P><P><EM>NI00121F000014, 06-Mar-2015</EM></P><P><EM>NI00144F0000098, 27-Sep-2014</EM></P><P><EM>NI00144F0000098, 05-Oct-2015</EM></P><P><EM>NI00121C000067, 18-May-2012</EM></P><P><EM>NI00121C000067, 18-May-2013</EM></P><P><EM>NI00121C000067, 18-May-2014</EM></P><P><EM>NI00121C000067, 18-May-2015</EM></P><P><EM>];</EM></P><P></P><P><EM>FinalTable:</EM></P><P><EM>LOAD </EM></P><P><EM>&nbsp; *,</EM></P><P><EM>&nbsp; <STRONG>AutoNumber(Right(PERIOD_FROM,4),POLICY_NO) as SEQ_NO</STRONG></EM></P><P><EM>Resident Table</EM></P><P><EM>Order By POLICY_NO, PERIOD_FROM;</EM></P><P></P><P><EM>DROP Table Table;</EM></P></BODY></HTML> Sat, 05 Mar 2016 08:48:33 GMT https://community.qlik.com/t5/QlikView/seq-no/m-p/1096645#M364271 maxgro 2016-03-05T08:48:33Z Re: seq_no https://community.qlik.com/t5/QlikView/seq-no/m-p/1096646#M364272 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>check this,</P><PRE __default_attr="plain" __jive_macro_name="code" class="jive_macro_code jive_text_macro _jivemacro_uid_14571706311436545" jivemacro_uid="_14571706311436545" modifiedtitle="true"> <P>Temp:</P> <P>LOAD * ,</P> <P>AutoNumber(Num(Date(Date#(PERIOD_FROM,'DD-MMM-YYYY'),'DD/MM/YYYY')),POLICY_NO) as SEQ_NO,</P> <P>Num(Date(Date#(PERIOD_FROM,'DD-MMM-YYYY'),'DD/MM/YYYY')) as DateNum</P> <P>INLINE [</P> <P>&nbsp;&nbsp;&nbsp; POLICY_NO, PERIOD_FROM</P> <P>&nbsp;&nbsp;&nbsp; NI00111C000039, 10-Feb-2012</P> <P>&nbsp;&nbsp;&nbsp; NI00111C000039, 10-Feb-2013</P> <P>&nbsp;&nbsp;&nbsp; NI00111C000039, 10-Feb-2014</P> <P>&nbsp;&nbsp;&nbsp; NI00121F000014, 06-Mar-2012</P> <P>&nbsp;&nbsp;&nbsp; NI00121F000014, 06-Mar-2013</P> <P>&nbsp;&nbsp;&nbsp; NI00121F000014, 06-Mar-2014</P> <P>&nbsp;&nbsp;&nbsp; NI00121F000014, 06-Mar-2015</P> <P>&nbsp;&nbsp;&nbsp; NI00144F0000098, 27-Sep-2014</P> <P>&nbsp;&nbsp;&nbsp; NI00144F0000098, 05-Oct-2015</P> <P>&nbsp;&nbsp;&nbsp; NI00121C000067, 18-May-2012</P> <P>&nbsp;&nbsp;&nbsp; NI00121C000067, 18-May-2013</P> <P>&nbsp;&nbsp;&nbsp; NI00121C000067, 18-May-2014</P> <P>&nbsp;&nbsp;&nbsp; NI00121C000067, 18-May-2015</P> <P>];</P> <P></P> <P>Table:</P> <P></P> <P></P> <P>LOAD *</P> <P>Resident Temp Order by POLICY_NO,SEQ_NO asc;</P> <P></P> <P></P> <P></P> </PRE><P></P><P><STRONG>Output:</STRONG></P><P><STRONG><IMG alt="Seqno Columns-208288.PNG" class="jive-image image-1" src="/legacyfs/online/117070_Seqno Columns-208288.PNG" style="height: auto; width: auto;" /></STRONG></P><P>HTH,</P><P>PFA,</P><P>Hirish</P></BODY></HTML> Sat, 05 Mar 2016 09:39:15 GMT https://community.qlik.com/t5/QlikView/seq-no/m-p/1096646#M364272 HirisH_V7 2016-03-05T09:39:15Z