Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
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

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