Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
just realised its right under my nose - USE THE PEEK!!!
oops