Variable / Field in AutoGenerate Command

[rakeshshah]

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

[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

[rakeshshah]

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

[rakeshshah]

just realised its right under my nose - USE THE PEEK!!!

oops