Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Dennis_Aguilar
Contributor
Contributor

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

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 casenrs 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
];

 

 

 

Labels (4)
1 Solution

Accepted Solutions
zhadrakas
Specialist II
Specialist II

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

View solution in original post

1 Reply
zhadrakas
Specialist II
Specialist II

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

View solution in original post