Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Ayoub
Contributor III
Contributor III

Loop on sql query

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

 

2 Solutions

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

Ayoub
Contributor III
Contributor III
Author

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 

View solution in original post

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

- Marcus

Ayoub
Contributor III
Contributor III
Author

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  

marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

Ayoub
Contributor III
Contributor III
Author

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 

View solution in original post