topic Re: Generate several rows for each row (with a new column) in QlikView

Generate several rows for each row (with a new column)

ok5454564 — Wed, 25 Jan 2023 15:39:06 GMT

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 ?)

Re: Generate several rows for each row (with a new column)

jcmachado — Wed, 25 Jan 2023 20:51:25 GMT

You can use the following approach to generate column C in your table:

Create a new table using the Resident load statement. This table will have the same number of rows as your original table, but with an additional column C.
In the Resident load statement, use a while loop and the rowno() function to iterate through the rows of your original table.
In the while loop, use a condition that compares the current row number to the max value of column B.
Inside the while loop, use the date from column A and the rowno() function to calculate the value for column C.
Use the Concatenate function to combine columns A, B, and C in the new 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.

Re: Generate several rows for each row (with a new column)

ok5454564 — Thu, 26 Jan 2023 12:20:11 GMT

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 ?

Re: Generate several rows for each row (with a new column)

jcmachado — Thu, 26 Jan 2023 21:01:10 GMT

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.