Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to generate the column C from a table witch has dates (like in column A) and a correspind number (rowno() ) in column B. I have of course only 6 rows in my table right now (from 01/01 to 06/01).
I don't see how to do that, probably by creating a new table and using Resident, but how can I use a "while" where condition depends of reaching the max value of B ? (or you see an other way ?)
You can use a combination of the resident load and a while loop to generate the column C based on the values in columns A and B.
Here's an example of how you can do this:
// Create a new table with the original data
Resident YourTable;
// Create a variable to store the current row number
let vCurrentRow = 1;
// Create a variable to store the maximum row number
let vMaxRow = max(B);
// Create a variable to store the current date
let vCurrentDate = peek(A, vCurrentRow);
// Use a while loop to iterate through the rows
while(vCurrentRow <= vMaxRow)
{
// Create a new column C with the current date
YourTable:
load
A as C
resident YourTable
where B = $(vCurrentRow);
// Increment the current row number
let vCurrentRow = vCurrentRow + 1;
}
In this example, the resident load is used to create a new table with the original data. Then, a while loop is used to iterate through the rows of the table based on the values in column B. Inside the while loop, a new column C is created with the current date from column A. The where condition is used to filter the rows based on the current row number stored in the vCurrentRow variable. Finally, the vCurrentRow variable is incremented at the end of each iteration so that the next row is processed on the next iteration.
Note that, this is a basic example, you may need to adjust the script to match your specific requirements and also you might need to handle the case where you have duplicate values in column B. Please let me know if you have any other question.
You can use the following approach to generate column C in your table:
Here is an example of how the script would look:
// Create new table with column C Temp: Load date as A, rowno() as B, While(rowno() <= max(B), if(rowno()=1,date,date(date+rowno()-1)) as C, resident [Original Table] );
//Concatenate all columns Concatenate(Temp) Load * Resident Temp;
This script creates a new table called "Temp" that has the same number of rows as the original table, but with the additional column C. The while loop iterates through the rows of the original table, using the rowno() function to compare the current row number to the max value of column B. The loop terminates once the current row number exceeds the max value of column B. The value for column C is calculated using the date from column A and the rowno() function inside the loop. Finally, it concatenates all the columns of the new table with the original table.
Please keep in mind that this script is a sample and that it may need to be adapted to your specific use case and data structure.
Thanks for your answer.
Your solution doesnt seem to be correct when i copy it and change value by mine, can you make a quick example ?
You can use a combination of the resident load and a while loop to generate the column C based on the values in columns A and B.
Here's an example of how you can do this:
// Create a new table with the original data
Resident YourTable;
// Create a variable to store the current row number
let vCurrentRow = 1;
// Create a variable to store the maximum row number
let vMaxRow = max(B);
// Create a variable to store the current date
let vCurrentDate = peek(A, vCurrentRow);
// Use a while loop to iterate through the rows
while(vCurrentRow <= vMaxRow)
{
// Create a new column C with the current date
YourTable:
load
A as C
resident YourTable
where B = $(vCurrentRow);
// Increment the current row number
let vCurrentRow = vCurrentRow + 1;
}
In this example, the resident load is used to create a new table with the original data. Then, a while loop is used to iterate through the rows of the table based on the values in column B. Inside the while loop, a new column C is created with the current date from column A. The where condition is used to filter the rows based on the current row number stored in the vCurrentRow variable. Finally, the vCurrentRow variable is incremented at the end of each iteration so that the next row is processed on the next iteration.
Note that, this is a basic example, you may need to adjust the script to match your specific requirements and also you might need to handle the case where you have duplicate values in column B. Please let me know if you have any other question.