3 Replies Latest reply: Jul 23, 2017 7:19 PM by Rakesh Shah RSS

    Variable / Field in AutoGenerate Command

    Rakesh Shah

      Hi

       

      I have a table which stores the count of Access Points we have per Store. What I would like to do is generate a unique asset for each one instead of a count. ie instead of one row per store and count of 20 access points, there is 20 rows with the store number and model in each row.

       

      I have the following and looking to find out how I get a dynamic number inside the auto generate.

       

      //Creating a temp table for loading all summary information in

      TempTable:

      LOAD

          "Store" as tempStore,

          Location as tempLocation,

          "AP Count" as tempAPCount, //this is the count of APs in the store

          Model as TempAssetModel

      FROM <File location>

       

      //Creating the final table with autogenerate as 1 just so the loop is an concatenate load

      FinalTable:

      Load

      null() as [Store Number]

      AutoGenerate(1);

       

      //Looping through each row in the table

      For counter = 1 to NoOfRows('TempTable')

      let autoGeneratecounter = num#(tempAPCount); //I've tried various iterations of the integer here

      Concatenate(FinalTable)

      Load

          tempStore as [Store Number],

              TempAssetModel as [Detailed Model],

              tempStore & TempAssetModel & RecNo() as [Asset Name]

          AutoGenerate(autoGeneratecounter);    //- how do i get the number inside the AutoGenerate to be "TempAPCount" Number??

      Next

        

      Thank you

      Rakesh

        • Re: Variable / Field in AutoGenerate Command
          Gysbert Wassenaar

          You're quite close. You need to use the peek function to get the value from the record in the temp table:

           

          //Looping through each row in the table

          For counter = 0 to NoOfRows('TempTable')-1

          let autoGeneratecounter = num#(Peek('tempAPCount' ,$(counter), 'TempTable')); //I've tried various iterations of the integer here

          Concatenate(FinalTable)

          Load

              tempStore as [Store Number],

                  TempAssetModel as [Detailed Model],

                  tempStore & TempAssetModel & RecNo() as [Asset Name]

              AutoGenerate($(autoGeneratecounter));    //- how do i get the number inside the AutoGenerate to be "TempAPCount" Number??

          Next

            • Re: Variable / Field in AutoGenerate Command
              Rakesh Shah

              Hi Gysbert,

               

              Thank you for your response. I have corrected as per your suggestions. The error I get now is that the three temp fields in the for loop are "not found" . This appears obvious as I can see the counter looping through the number of rows - but not where im loading the data of each row.

              How do I load the data?

               

              TempTable:

              LOAD

                  "Store" as tempStore,

                  "AP Count" as tempAPCount,

                  Model as TempAssetModel

              FROM [lib://TEch/All_Access_Points.xlsx]

              (ooxml, embedded labels, header is 2 lines, table is Cisco);

               

              FinalTable:

              Load

              null() as [Store Number]

              AutoGenerate(1);

               

               

              For counter = 0 to NoOfRows('TempTable')-1

                   let autoGeneratecounter = num#(Peek('tempAPCount',$(counter),'TempTable'));

                   Concatenate(FinalTable)

                   Load

                        tempStore as [Store Number],

                        TempAssetModel as [Detailed Model],

                        tempStore & TempAssetModel & RecNo() as [Asset Name]   

                   AutoGenerate($(autoGeneratecounter));

              Next

                

              drop table TempTable;

               

               

              If it helps im trying to load the data to look like this

               

              FROM

              Site     Model          Count

              1234   AP-ABC      4

              7890   AP-ZYX       3

               

              TO

              Site     Model          Asset Name

              1234   AP-ABC     1234-AP-ABC-1

              1234   AP-ABC     1234-AP-ABC-2

              1234   AP-ABC     1234-AP-ABC-3

              1234   AP-ABC     1234-AP-ABC-4

              7890   AP-XYZ     7890-AP-XYZ-1

              7890   AP-XYZ     7890-AP-XYZ-2

              7890   AP-XYZ     7890-AP-XYZ-3