Creating new columns that go from 0 to the value in the variable of a table.

Dennis_Aguilar — Sat, 16 Nov 2024 21:00:44 GMT

I have a database that when grouped by the variables of gender, age, and age_partner result in a very similar table as "general_table". With the slight difference that "general_table" has the variables max_age and nrs. I know how to add those variables, that is not an issue here.

general_table:
LOAD * INLINE [
    case,gender, age, age_partner, max_age, nrs
    1, female, 18, 22, 60, 42
    2, female, 19, 20, 60, 41
    3, male, 18, 17, 65, 47
];

How do I create a new table from a previous table, that takes the value of nrs, and creates a column variable, called n, that goes from 0 to the value in nrs?

To illustrate further, in case 1 nrs is equal to 42 so the column would go from 0 to 42, in case 2 nrs is equal to 41 so the column would go from 0 to 41, and the same for case 3.

I have inserted the code of the desired output, I've labeled it as "wanted_table"

PS: I did this in R by using for loops, lists, and bind_rows.

wanted_table:
LOAD * INLINE [
    case, gender, age, age_partner, max_age, n
    1, female, 18, 22, 60, 0
    1, female, 18, 22, 60, 1
    1, female, 18, 22, 60, 2
    1, female, 18, 22, 60, 3
    1, female, 18, 22, 60, 4
    1, female, 18, 22, 60, 5
    1, female, 18, 22, 60, 6
    1, female, 18, 22, 60, 7
    1, female, 18, 22, 60, 8
    1, female, 18, 22, 60, 9
    1, female, 18, 22, 60, 10
    1, female, 18, 22, 60, 11
    1, female, 18, 22, 60, 12
    1, female, 18, 22, 60, 13
    1, female, 18, 22, 60, 14
    1, female, 18, 22, 60, 15
    1, female, 18, 22, 60, 16
    1, female, 18, 22, 60, 17
    1, female, 18, 22, 60, 18
    1, female, 18, 22, 60, 19
    1, female, 18, 22, 60, 20
    1, female, 18, 22, 60, 21
    1, female, 18, 22, 60, 22
    1, female, 18, 22, 60, 23
    1, female, 18, 22, 60, 24
    1, female, 18, 22, 60, 25
    1, female, 18, 22, 60, 26
    1, female, 18, 22, 60, 27
    1, female, 18, 22, 60, 28
    1, female, 18, 22, 60, 29
    1, female, 18, 22, 60, 30
    1, female, 18, 22, 60, 31
    1, female, 18, 22, 60, 32
    1, female, 18, 22, 60, 33
    1, female, 18, 22, 60, 34
    1, female, 18, 22, 60, 35
    1, female, 18, 22, 60, 36
    1, female, 18, 22, 60, 37
    1, female, 18, 22, 60, 38
    1, female, 18, 22, 60, 39
    1, female, 18, 22, 60, 40
    1, female, 18, 22, 60, 41
    1, female, 18, 22, 60, 42
    2, female, 19, 20, 60, 0
    2, female, 19, 20, 60, 1
    2, female, 19, 20, 60, 2
    2, female, 19, 20, 60, 3
    2, female, 19, 20, 60, 4
    2, female, 19, 20, 60, 5
    2, female, 19, 20, 60, 6
    2, female, 19, 20, 60, 7
    2, female, 19, 20, 60, 8
    2, female, 19, 20, 60, 9
    2, female, 19, 20, 60, 10
    2, female, 19, 20, 60, 11
    2, female, 19, 20, 60, 12
    2, female, 19, 20, 60, 13
    2, female, 19, 20, 60, 14
    2, female, 19, 20, 60, 15
    2, female, 19, 20, 60, 16
    2, female, 19, 20, 60, 17
    2, female, 19, 20, 60, 18
    2, female, 19, 20, 60, 19
    2, female, 19, 20, 60, 20
    2, female, 19, 20, 60, 21
    2, female, 19, 20, 60, 22
    2, female, 19, 20, 60, 23
    2, female, 19, 20, 60, 24
    2, female, 19, 20, 60, 25
    2, female, 19, 20, 60, 26
    2, female, 19, 20, 60, 27
    2, female, 19, 20, 60, 28
    2, female, 19, 20, 60, 29
    2, female, 19, 20, 60, 30
    2, female, 19, 20, 60, 31
    2, female, 19, 20, 60, 32
    2, female, 19, 20, 60, 33
    2, female, 19, 20, 60, 34
    2, female, 19, 20, 60, 35
    2, female, 19, 20, 60, 36
    2, female, 19, 20, 60, 37
    2, female, 19, 20, 60, 38
    2, female, 19, 20, 60, 39
    2, female, 19, 20, 60, 40
    2, female, 19, 20, 60, 41
    3, male, 18, 17, 65, 0
    3, male, 18, 17, 65, 1
    3, male, 18, 17, 65, 2
    3, male, 18, 17, 65, 3
    3, male, 18, 17, 65, 4
    3, male, 18, 17, 65, 5
    3, male, 18, 17, 65, 6
    3, male, 18, 17, 65, 7
    3, male, 18, 17, 65, 8
    3, male, 18, 17, 65, 9
    3, male, 18, 17, 65, 10
    3, male, 18, 17, 65, 11
    3, male, 18, 17, 65, 12
    3, male, 18, 17, 65, 13
    3, male, 18, 17, 65, 14
    3, male, 18, 17, 65, 15
    3, male, 18, 17, 65, 16
    3, male, 18, 17, 65, 17
    3, male, 18, 17, 65, 18
    3, male, 18, 17, 65, 19
    3, male, 18, 17, 65, 20
    3, male, 18, 17, 65, 21
    3, male, 18, 17, 65, 22
    3, male, 18, 17, 65, 23
    3, male, 18, 17, 65, 24
    3, male, 18, 17, 65, 25
    3, male, 18, 17, 65, 26
    3, male, 18, 17, 65, 27
    3, male, 18, 17, 65, 28
    3, male, 18, 17, 65, 29
    3, male, 18, 17, 65, 30
    3, male, 18, 17, 65, 31
    3, male, 18, 17, 65, 32
    3, male, 18, 17, 65, 33
    3, male, 18, 17, 65, 34
    3, male, 18, 17, 65, 35
    3, male, 18, 17, 65, 36
    3, male, 18, 17, 65, 37
    3, male, 18, 17, 65, 38
    3, male, 18, 17, 65, 39
    3, male, 18, 17, 65, 40
    3, male, 18, 17, 65, 41
    3, male, 18, 17, 65, 42
    3, male, 18, 17, 65, 43
    3, male, 18, 17, 65, 44
    3, male, 18, 17, 65, 45
    3, male, 18, 17, 65, 46
    3, male, 18, 17, 65, 47
];

Re: Creating new columns that go from 0 to the value in the variable of a table.

zhadrakas — Mon, 29 Apr 2019 11:06:06 GMT

here is a script that should do what you want:

general_table:
LOAD * INLINE [
case,gender, age, age_partner, max_age, nrs
1, female, 18, 22, 60, 42
2, female, 19, 20, 60, 41
3, male, 18, 17, 65, 47
];

//loop through each case
For iCase = 0 to NoOfRows('general_table')-1
//save current column values
LET vCase = peek('case', $(iCase), 'general_table');
LET vGender = peek('gender', $(iCase), 'general_table');
LET vAge = peek('age', $(iCase), 'general_table');
LET vAgePartner = peek('age_partner', $(iCase), 'general_table');
LET vMaxAge = peek('max_age', $(iCase), 'general_table');
LET vNrs = peek('nrs', $(iCase), 'general_table');

//loop through each number from 0 to nrs-value
For iNrs = 0 to $(vNrs)

//create lines
TRACE $(vCase) as case, '$(vGender)' as gender, $(vAge) as age,$(vAgePartner) as age_partner,$(vMaxAge) as max_age, $(iNrs) as nrs;
Concatenate (general_table)
Load $(vCase) as case,
'$(vGender)' as gender,
$(vAge) as age,
$(vAgePartner) as age_partner,
$(vMaxAge) as max_age,
$(iNrs) as nrs
AutoGenerate (1);

next iNrs
next iCase

topic Creating new columns that go from 0 to the value in the variable of a table. in QlikView

Creating new columns that go from 0 to the value in the variable of a table.

Re: Creating new columns that go from 0 to the value in the variable of a table.