11 Replies Latest reply: Nov 1, 2016 4:17 AM by Rahul Krishan Goyal RSS

    custom counter in load script

    Rahul Krishan Goyal

      Hi,

       

      Please help me.

       

      I am trying to run a custom counter using a variable 'x' with incremental value 1 within a load scripts which is loading data from a resident table .

       

      Please someone respond. It's urgent.

       

      Thank You,

       

      Rahul Krishan Goyal

        • Re: custom counter in load script
          bruno bertels

          not sure about what you need exactly but have a look at autonumber function

           

          can be somethink like that :

           

          LOAD

          if(sales>0,autonumber("date"))as ID,

           

          // rangesum(sales,0,RowNo()) as cumul_vente,

              "date",

              sales

          FROM [lib://Help Test/autonumber.xls]

          (biff, embedded labels, table is Feuil1$);

            • Re: custom counter in load script
              Rahul Krishan Goyal

              Hi  Bruno,

               

              Thanks so much for the response.

               

              I am actually looking to run a custom counter using a variable with incremental value 1 within a load statement loading data from a resident table. 

               

              I should be able to set value for the counter.

               

              Please help,

                • Re: custom counter in load script
                  Rahul Krishan Goyal

                  In autonumber function, you cannot control the next value. While I want to set value to zero on a particular if condition and want to restart the value with 1.

                   

                  Thanks,

                    • Re: custom counter in load script
                      Sunny Talwar

                      What exactly are you doing Rahul? You need to provide some context here... May be a sample with the expected outcome?

                        • Re: custom counter in load script
                          Rahul Krishan Goyal

                          Hi Sunny,

                          Thanks so much for the response.

                          Example;

                          I have table a

                          A:

                          Load

                          no,

                          date,

                          time

                          The table A is sorted on Name,date & time.  There a more than one records no wise and date wise.

                           

                          I want to use this table as resident table for another new table having the similar sorting as table A . I want to run a counter within second load statement having resident clause  starting from 1(not using rowno() or rangesome), and, will capture counter value in a field.

                          I will pick all the fields from resident table A, will check the time difference for a particular no.(current time - previous tome). and, if  the difference is more than 'x' time, I want to reset the counter to zero will capture this zero value in the same field I am capturing counter values above. If the time difference is less than 'x', the counter value will remain same. The counter will restart with zero for next record from resident table.

                           

                          Hope, now it is clear to you.

                           

                          Thanks,

                            • Re: custom counter in load script
                              Rahul Krishan Goyal

                              Sunny,

                               

                              I actually wants to run a custom counter within a load statement loading data from resident table so that I can manipulate the counter value based on my if conditions. Currently, I am unable to do so as my counter value does not change even after loading all the data from resident table.

                               

                              Thanks,

                                • Re: custom counter in load script
                                  Sunny Talwar

                                  Seems like you need to use Peek/Previous here... Peek() vs Previous() – When to Use Each

                                  Peek() or Previous() ?

                                   

                                  Have you already tried those?

                                    • Re: custom counter in load script
                                      Rahul Krishan Goyal

                                      Hi Sunny,

                                       

                                      I have already gone thru the link you have sent.

                                      'previous' function I am already using in my second load statement for comparing previous and current time.

                                      As communicated to you, I am facing problem in running a custom counter while fetching records from a resident table.

                                      Let's say, I have 1000 records in my table 'A', and, I want to fetch all records in second table using 'resident' and a custom counter with incremental value 1. I will manipulate the counter value based on my 'if' condition. 

                                       

                                      Thanks,

                                        • Re: custom counter in load script
                                          Sunny Talwar

                                          I kind of understand what you want Rahul, but without looking at what you have already tried or without a sample with expected output, I can only give you generic direction. If you want more help, you will have to provide me with a numeric sample (not just words) and what do you want to see in the end after the reload completes.

                                           

                                          Does this make sense my friend?

                                            • Re: custom counter in load script
                                              Rahul Krishan Goyal

                                              Hi Sunny,

                                               

                                              Following are the tables;

                                               

                                              A_FF_ONE:

                                              LOAD

                                              f_tgt as ff_tgt,

                                              f_source as ff_source,

                                              f_SSID as ff_SSID,

                                              f_rssi as ff_rssi,

                                              f_imp_day as ff_imp_day,

                                              f_imp_date as ff_imp_date,

                                              f_imp_time as ff_imp_time,

                                              f_imp_year as ff_imp_year,

                                              ff_time as fff_time,

                                              if (ff_time > 6,1,0) as return_cust

                                              order by f_source,f_imp_date,f_imp_time;

                                               

                                              let custom_counter=1;

                                              A_FULL_FINAL:

                                              Load

                                              ff_source as fff_source,

                                              ff_SSID as fff_ssid,

                                              ff_imp_date as fff_imp_date,

                                              ff_imp_time as fff_imp_time,

                                              return_cust as f_return_cust,

                                              $custom_counter as cust_count,

                                              if((return_cust=1 and fff_time > 6 and ff_source=previous(ff_source)),custom_counter=custom_counter+1

                                              if(return_cust=0 and fff_time < 6 and ff_source=previous(ff_source),custom_counter)) as f_source_cntr

                                              resident A_FF_ONE

                                              order by ff_source,ff_imp_date,ff_imp_time;

                                              custom_counter=custom_counter+1;

                                               

                                              Now I want to run a custom counter as 'custom_counter' with incremental 1 in table 'A_FULL_FINAL'  so that I capture the required values of custom_counter in f_source_cntr.

                                               

                                              So the issue is, currently, the custom counter is giving me value 1 only for all the records fetched from table A_FF_ONE.

                                               

                                              Please help.

                                               

                                              Again I would like to clarify that I want to run a counter in second table with incremental one without using recno() or rangesum.

                                               

                                              Thanks,