Re: Performance IF THEN ELSE - Qlik Community

Report Inappropriate Content · ‎2011-10-24

Hello,

I have several IF THEN ELSE in my script, and I have response times far too long.
Do you have an idea to improve response times? Thank you in advance for your help

its_anandrjs · ‎2011-10-24

Hi,

Try to split or break your conditions in varibles and then use it in conditions or in place of this use SET analysis script.

Rgds

Anand

Report Inappropriate Content · ‎2011-10-24

Hi Anand,

Thank you for tour response...

Have you an example to explain your idea please?

I'm not sure to understand you wou say...thank you

erichshiino · ‎2011-10-24

Hi, hrenaudineau

If you post part of your script, we can try to evaluate if your performance problem is related to the if-statement or something else.

Regards,

Erich

Report Inappropriate Content · ‎2011-10-24

Hi Erich,

This is a part of my script...

I have more than 10 franges...

//chargement de la premiere ligne de la table

vIndiv = Text(Peek('HPaieTemp2_Indiv',0,'HeurePaie_Temp2'))

vChant = Text(Peek('HPaieTemp2_Co_Aff',0,'HeurePaie_Temp2'))

vChantElem = Text(Peek('HPaieTemp2_Co_Cha_Elem',0,'HeurePaie_Temp2'))

vCoMac = Text(Peek('HPaieTemp2_Co_Mach',0,'HeurePaie_Temp2'))

vDate = Date(Peek('HPaieTemp2_DatGlob',0,'HeurePaie_Temp2'))

vSem = Num(Peek('HPaieTemp2_Semaine_Calcul',0,'HeurePaie_Temp2'))

vIndHis = Text(Peek('HPaieTemp2_Indiv_Histo',0,'HeurePaie_Temp2'))

vStaHis = Text(Peek('HPaieTemp2_Stat_Histo',0,'HeurePaie_Temp2'))

vSocHis = Text(Peek('HPaieTemp2_Soc_Histo',0,'HeurePaie_Temp2'))

vAnaHis = Text(Peek('HPaieTemp2_Co_Analy_Histo',0,'HeurePaie_Temp2'))

vSoIHis = Text(Peek('HPaieTemp2_Soc_Int_Histo',0,'HeurePaie_Temp2'))

vDonnee = Peek('HPaieTemp2_DonnCalcul',0,'HeurePaie_Temp2')

vRegIndic = Text(Peek('HPaieTemp2_Regp',0,'HeurePaie_Temp2'))

vNbHrContrat = Num(Peek('HPaieTemp2_H_Contr',0,'HeurePaie_Temp2'))

vMoisTrait = Text(Peek('HPaieTemp2_Mois_Traitem',0,'HeurePaie_Temp2'))

for vNumLig = 1 to $(vNbLignHeure) //pour chaque ligne de données

vIndivPrec = Text($(vIndiv)) //initialisation de l'indiv precedent avec la valeur de l'indiv

vMmIndiv = 0 // test pour un même individu

DO WHILE vMmIndiv =0 //boucle pour un même individu

vSemPrec = $(vSem)

vCptHeure = 0

vMmSem = 0

DO WHILE vMmSem = 0

vDebFrange = '$(vCptHeure)'

vFinFrange = NumSum('$(vCptHeure)','$(vDonnee)')

//Frange 1

IF vFinFrange <= vNbHrContrat THEN

IF vRegIndic = Text('HeureJour') THEN

Nv_Code_Point = 'HN';

ELSE

IF vRegIndic = Text('HeureNuit') THEN

Nv_Code_Point = 'HNM100';

ELSE

IF vRegIndic = Text('HeureFerie') THEN

Nv_Code_Point = 'HFM100';

ELSE

IF vRegIndic = Text('HeureMDimanche') THEN

Nv_Code_Point = 'HDM100';

ENDIF

Nv_Nb_Hr = '$(vDonnee)';

CALL Table_Heure ('$(vIndiv)','$(vChant)','$(vChantElem)','$(vCoMac)','$(vDate)','$(vSem)','$(vIndHis)','$(vStaHis)','$(vSocHis)','$(vAnaHis)','$(vSoIHis)','$(vMoisTrait)','$(Nv_Code_Point)','$(Nv_Nb_Hr)')

//Frange 2

ELSE

IF vDebFrange >= vNbHrContrat and vFinFrange<=vTepa25 THEN

IF vRegIndic = Text('HeureJour') THEN

Nv_Code_Point = 'HSJ25';

ELSE

IF vRegIndic = Text('HeureNuit') THEN

Nv_Code_Point = 'HSN25';

ELSE

IF vRegIndic = Text('HeureMDimanche') THEN

Nv_Code_Point = 'HSD25';

ENDIF

Nv_Nb_Hr = '$(vDonnee)';

CALL Table_Heure ('$(vIndiv)','$(vChant)','$(vChantElem)','$(vCoMac)','$(vDate)','$(vSem)','$(vIndHis)','$(vStaHis)','$(vSocHis)','$(vAnaHis)','$(vSoIHis)','$(vMoisTrait)','$(Nv_Code_Point)','$(Nv_Nb_Hr)')

//Frange 3

ELSE

IF vDebFrange >= vTepa25 and vFinFrange<=vTepa50 THEN

IF vRegIndic = Text('HeureJour') THEN

Nv_Code_Point = 'HSJ50';

ELSE

IF vRegIndic = Text('HeureNuit') THEN

Nv_Code_Point = 'HSN50';

ELSE

IF vRegIndic = Text('HeureMDimanche') THEN

Nv_Code_Point = 'HSD50';

ENDIF

Nv_Nb_Hr = '$(vDonnee)';

CALL Table_Heure ('$(vIndiv)','$(vChant)','$(vChantElem)','$(vCoMac)','$(vDate)','$(vSem)','$(vIndHis)','$(vStaHis)','$(vSocHis)','$(vAnaHis)','$(vSoIHis)','$(vMoisTrait)','$(Nv_Code_Point)','$(Nv_Nb_Hr)')

ENDIF

vCptHeure = '$(vFinFrange)'

vNumLig = Num($(vNumLig))+1

IF vNumLig < vNbLignHeure THEN

vIndiv = Text(Peek('HPaieTemp2_Indiv',$(vNumLig),'HeurePaie_Temp2'))

vChant = Text(Peek('HPaieTemp2_Co_Aff',$(vNumLig),'HeurePaie_Temp2'))

vChantElem = Text(Peek('HPaieTemp2_Co_Cha_Elem',$(vNumLig),'HeurePaie_Temp2'))

vCoMac = Text(Peek('HPaieTemp2_Co_Mach',$(vNumLig),'HeurePaie_Temp2'))

vDate = Date(Peek('HPaieTemp2_DatGlob',$(vNumLig),'HeurePaie_Temp2'))

vSem = Num(Peek('HPaieTemp2_Semaine_Calcul',$(vNumLig),'HeurePaie_Temp2'))

vIndHis = Text(Peek('HPaieTemp2_Indiv_Histo',$(vNumLig),'HeurePaie_Temp2'))

vStaHis = Text(Peek('HPaieTemp2_Stat_Histo',$(vNumLig),'HeurePaie_Temp2'))

vSocHis = Text(Peek('HPaieTemp2_Soc_Histo',$(vNumLig),'HeurePaie_Temp2'))

vAnaHis = Text(Peek('HPaieTemp2_Co_Analy_Histo',$(vNumLig),'HeurePaie_Temp2'))

vSoIHis = Text(Peek('HPaieTemp2_Soc_Int_Histo',$(vNumLig),'HeurePaie_Temp2'))

vDonnee = Peek('HPaieTemp2_DonnCalcul',$(vNumLig),'HeurePaie_Temp2')

vRegIndic = Text(Peek('HPaieTemp2_Regp',$(vNumLig),'HeurePaie_Temp2'))

vNbHrContrat = Num(Peek('HPaieTemp2_H_Contr',$(vNumLig),'HeurePaie_Temp2'))

vMoisTrait = Text(Peek('HPaieTemp2_Mois_Traitem',$(vNumLig),'HeurePaie_Temp2'))

ENDIF

IF vSem = vSemPrec and vIndiv = vIndivPrec THEN

vMmSem = 0;

ELSE

vMmSem = 1;

ENDIF

LOOP

IF vIndiv = vIndivPrec THEN

vMmIndiv = 0;

ELSE

vMmIndiv = 1;

ENDIF

LOOP vNumLig = $(vNumLig) -1;

NEXT vNumLig

Report Inappropriate Content · ‎2011-10-24

Any idea someone???

llauses243 · ‎2011-10-24

Hi,

In general to use IF degrade performance

My offer is:

1.- Replace casuistic using new table in script (contains franges)

2.- If routine contains LOAD then can replace joining with table of pams.

Pls to see .xls attached with anz of your partial script

Good luck, Luis

Report Inappropriate Content · ‎2011-10-25

Hi Luis,

Thank you for your help...

Can you explain more precisely the idea with you? I have not understand everything ...
I really thank you for your help.

llauses243 · ‎2011-10-25

Hi,try to be more clear ...

1.- for you the case is about script load (improvement)? i believe yes

2.- for you to use IF in script degrade perfomance? for me yes

3.- the routine called (Table_Heure) contains command sql LOAD or SELECT to database ?

4.- In the script read all rows (through casuistic FOR, LOOP, IF) of table HeurePaie_Temp2 ?

Then is possible ...

1.- Create table through SELECT INLINE for omitt casuistic, this is base for no to use IF

2.- Build command sql LOAD based on JOIN or variant (with 2 tables & database) for simply load

I hope the discussion gives

Good luck, Luis

P.D. I don't know detail about routine, if you like and can ... do you to share?