Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmark1990
Contributor III
Contributor III

Loop within LOAD statement to prevent repeating code

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.

2 Replies
MarcoWedel

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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