Load For loop to load separately each file from folder // apply transformation in each file separately // then concatenate table

brunobertels — Mon, 29 Jan 2024 16:21:26 GMT

Hi All

I have a script that was initialy load all excel files from folder

Then apply transformations ( to filter empty lines , add dimension , calculated field etc ) that was working fine.

I need now to use for loop statement to load each files separately to apply transformation on each file separately then concatenate all in one simple table.

The challenge here is one of the step of the transformation consist in a temp load with a where clause using variable. ( with peek function to concat some value in a variable then use the result in a where clause )

It occurs that for certain file I need this where clause statement but for others i do not need this where clause.

Can someone help me add the code to do a for loop ( with some "for each file in ...load ...from ... next file" )

And also for the step with the where clause add an if statement with some think like

If ... load ... Then .... load ... end if .. to load either the temp table with the where clause or not

And at the end concatenate in one table each file ?

so to resume my need

I need to load each file from folder

apply some tranformation in each of them

create value list in variable to use it in a where clause but only if this list is populated if not i don't need it

Here below my actual Code :

// load data as it is in Files
[brut]:
LOAD
[F1], // date et nom campagne
[F2], // Famille ou sous famille
[F5], // Volume
[F9], // ACL famille si idem sous famille
[F10], // ACL sous famille
[F11], //ACW famille si idem sous famille
[F12] // ACW sous famille
FROM [lib://CRC_2024 (groupe_u45410)/SUIVI_QUALIF/*_QUALIF.xls]
(biff, embedded labels, table is Sheet1$)
Where WildMatch([F1],'Campagne*','Le *') OR isnull([F1]) ;// to avoid empty some empty lines

NoConcatenate
// create dimension
temp:

Load
//create DATE
if(wildmatch(left([F1],3),'Le '),
date(date#(right([F1],10),'DD/MM/YYYY')),peek(DATE_JOUR)) as DATE_JOUR,

// create CAMPAIN and campain ID as SDA then mother familly and child familly
if(wildmatch([F1],'*Campagne*'),[F1],peek(CAMPAGNE)) as CAMPAGNE,
if(wildmatch([F1],'*Campagne*'),Trim(left(Trim(mid([F1], 8 + 1)), 5)),peek(SDA)) as SDA,
if( not wildmatch([F2],'- *'),[F2],peek(Famille) ) as Famille, [F2] as sous_Famille,

// récupére le volume
[F5] as Volume,

//Temps de conversation (volume * acl moyen)
[F5]*if(wildmatch([F9],'*h*'),
replace(replace([F9],'h',':'),chr(39),':'),
replace(replace([F10],'h',':'),chr(39),':')) as TempsACL,

//Temps de post appel (volume * acw moyen)
[F5]*if(wildmatch([F11],'*h*'),
replace(replace([F11],'h',':'),chr(39),':'),
replace(replace([F12],'h',':'),chr(39),':')) as TempsACW

resident brut;

// need to excluse chr 39 in field Famille sous_famille to avoid further issue in variable vListeValeurKEY
temp1:
NoConcatenate
load
DATE_JOUR,
CAMPAGNE,
SDA,
PurgeChar(Famille,chr(39)) as Famille,
Purgechar(sous_Famille,chr(39)) as sous_Famille,
Volume,
TempsACL,
TempsACW

resident temp;

//temp table to create list of value for next use in where clause load with variable vListeValeurKEY
noconcatenate
VarSSFamille:
load
Famille
//if(wildMatch(sous_Famille,'- *'),Famille,null()) as Famille
resident temp1 where wildMatch(sous_Famille,'- *') and previous(Famille)=Famille;

Load
if(wildMatch(sous_Famille,'- *'),
concat( distinct chr(39)&Famille&chr(39),',')) as ListeValeurKEY
resident VarSSFamille where not IsNull(Famille);

let vListeValeurKEY =peek('ListeValeurKEY',-1,ListeValeurKEY);

noconcatenate
// load and filter with a where clause using variable vListeValeurKEY
FINAL:
load*
// Resident Final
Resident temp1
where isnum(Volume) and Volume>0
//i need this where clause if vListeValeurKEY as value , if not i don't need the where clause :
and not wildmatch(sous_Famille,$(vListeValeurKEY));

drop table temp;
drop table temp1;
drop table brut;
drop table VarSSFamille;

Regards

topic Load For loop to load separately each file from folder // apply transformation in each file separately // then concatenate table in App Development

Load For loop to load separately each file from folder // apply transformation in each file separately // then concatenate table