Loop within LOAD statement to prevent repeating code

[qlikmark1990]

Currently I have the following part of my script :

SUB SplitsenNaarMiddel(middel, eersteVraag, laatsteVraag)

FOR vraagNummers = eersteVraag TO laatsteVraag

IF(vraagNummers > 99) THEN

SET output = $(vraagNummers);

ELSEIF (vraagNummer > 9) THEN

SET output = 0$(vraagNummers);

ELSE

SET output = 00$(vraagNummers);

END IF;

TRACE $(vraagNummers);

TRACE $(output);

NEXT

Temp_Middel_$(middel):

LOAD

Veldzoeknaam AS ZoekNaam,

VeldP_id AS $(middel)_VeldP_id,

Middel AS $(middel)_Middel,

MATEnr AS $(middel)_MATE,

IF(Vraagnr=1, VeldWaarde) AS $(middel)_Vraag001,

IF(Vraagnr=2, VeldWaarde) AS $(middel)_Vraag002,

IF(Vraagnr=3, VeldWaarde) AS $(middel)_Vraag003,

IF(Vraagnr=4, VeldWaarde) AS $(middel)_Vraag004,

IF(Vraagnr=5, VeldWaarde) AS $(middel)_Vraag005,

IF(Vraagnr=6, VeldWaarde) AS $(middel)_Vraag006,

IF(Vraagnr=1, VeldTypeGrp) AS $(middel)_Vraag001Type,

IF(Vraagnr=2, VeldTypeGrp) AS $(middel)_Vraag002Type,

IF(Vraagnr=3, VeldTypeGrp) AS $(middel)_Vraag003Type,

IF(Vraagnr=4, VeldTypeGrp) AS $(middel)_Vraag004Type,

IF(Vraagnr=5, VeldTypeGrp) AS $(middel)_Vraag005Type,

IF(Vraagnr=6, VeldTypeGrp) AS $(middel)_Vraag006Type

RESIDENT Data2

WHERE Vraagsort = '$(middel)';

END SUB

My function call is :

CALL SplitsenNaarMiddel('A', 1, 6)

The working of my script is correct but I want to replace my repeating code by using the parameters 1 and 6. 'vraagNummers' are the values 1, 2, 3, 4, 5 and 6 and 'output' 001, 002, 003, 004, 005 and 006 with leading zeroes. I'd like to repeat the rule 'IF(Vraagnr=1, VeldWaarde) AS $(middel)_Vraag001' 6 times with the variables described in the loop and 'IF(Vraagnr=1, VeldTypeGrp) AS $(middel)_Vraag001Type' with the same variables. My code needs to work with each possible number of iteration and start numbers other than 1, like 10 to 20 for example.

[MarcoWedel]

Do you have some sample data and your expected result to test a solution with?

[marcus_sommer]

Within the load itself you couldn't check/react if there is / should be any fields. This meant you need to build the load-statement on the outside from the load with something like:

FOR vraagNummers = eersteVraag TO laatsteVraag

     let vLoadStatement1 = '$(vLoadStatement1)' &

                                          'IF(Vraagnr=' & $(vraagNummers) &

                                          ', VeldWaarde) AS ' & $(middel) & num($(vraagNummers), '000') &

                                          ', ';

....

The same for second field and you need further a counter and/or replace-logic for the last comma and similar things to get in the end a valid load-statement within a single variable (using several variables which are concatenated in the end will be easier than doing it within a single variable). Further I suggest to do it first without an executing of the load else just checking the created load-statement within the trace, log-file and/or a textbox.

Beside this with your approach you are creating multiple different crosstables - quite often is the handling of crosstables much more expensive than using "normale" data-structures. Therefore are you really sure that you need them?

- Marcus