topic Re: Loop on sql query in QlikView

Loop on sql query

Ayoub — Sat, 16 Nov 2024 02:08:15 GMT

Hey community,

I have a strange Issue and i can not found the prob, Here's the detail .

i Extracted all Data into first table called it Data with 2000 rows (2000 code client).

I have already 2 filter created st_0 & st_1 each one contains 1000 ID or as i called it Code_Client.

So i have to Extract data from different table where code client in st_0 & st_1, but in the end of the script i have only the details of the first itération (St_0)..

the code below is the extraction i want to do.

______________________________________________________________________

Data:
LOAD
CLICODECLI as Code_client_Orc,
date(CLIDJJETAT&'/'&CLIDMMETAT&'/'&CLIDAAETAT) as DATE_ETAT,
CLINUMETEL as N°_TÉL
;

SQl Select
CLICODECLI,
CLIDJJETAT,
CLIDMMETAT,
CLIDAAETAT,
CLINUMETEL
From REFTIERMAW.REFCLIPP00 where CLIDMMETAT=2 and CLIDAAETAT=2018 ;

for b=0 to 1 step 1

/* Tiers PP*/
left join(Data)
LOAD
CLPCODECLI as Code_client_Orc ,
CLPNOMCLIP as NOM_CLIENT,
CLPPRECLIP as PRENOM_CLIENT;

SQl Select

CLPCODECLI,
CLPNOMCLIP,
CLPPRECLIP

From REFTIERMAW.REFCLPPP00 where CLPCODECLI in $(st_$(b)) ;

/*Tiers PM*/
left join(Data)
LOAD
CLMCODECLI as Code_client_Orc,
CLMNBRSALR as CAPITAL;

SQl select
CLMCODECLI,
CLMNBRSALR

From REFTIERMAW.REFCLMPP00 where CLMCODECLI in $(st_$(b)) ;

/* Adresse */
left join(Data)
LOAD

ADCCODECLI as Code_client_Orc,
ADCLIBLADC as LIBELLE_ADRESSE,
ADCCODEVIL as CODE_VILLE,
ADCCODEPAY as CODE_PAYS;

SQL Select
ADCCODECLI,
ADCLIBLADC,
ADCCODEVIL,
ADCCODEPAY

From REFTIERMAW.REFADCPP00 where ADCCODECLI in $(st_$(b));

NEXT

Re: Loop on sql query

marcus_sommer — Thu, 24 Oct 2019 06:59:59 GMT

Usually you couldn't join within a loop because your added fields from the first iteration will be considered within the next iterations ...

- Marcus

Re: Loop on sql query

Ayoub — Thu, 24 Oct 2019 13:25:52 GMT

Hi @marcus_sommer Marcus,once again thank you for your help 😄

So you say there's no solution for that problem ? otherwise can u suggest me something to do ..

thanks again

Re: Loop on sql query

marcus_sommer — Thu, 24 Oct 2019 14:50:48 GMT

You may load it seperately like: first load + joins then second load + joins and then concatenate the first and the second part. Without knowing your data and requirements it's difficult to say more but somehow your tried approach looked rather unusual.

- Marcus

Re: Loop on sql query

Ayoub — Thu, 24 Oct 2019 15:23:25 GMT

Huge thanks to you @marcus_sommer .

Worked like magic .. i stored every iteration in deferent table and dropped the table and filters from the application and it's working 😄 Thank you again