Bonjour,
Il y a sans doute plus simple mais voici une solution qui fonctionne :
Data:
Load * inline
[
START_DATE,END_DATE,ID_WEEK
14/03/2011,16/03/2011,M105587L25725
14/03/2011,30/03/2011,M100337L41098;M105579L25724;M104310L30703;
];
Table:
NoConcatenate
LOAD *,
len(ID_WEEK)-len(PurgeChar(ID_WEEK,';')) as NB_SEPARATEUR
Resident Data;
DROP Table Data;
TableTemp:
NoConcatenate
LOAD *
Resident Table
Order By NB_SEPARATEUR;
LET vMax = Num(peek('NB_SEPARATEUR',-1,'TableTemp'));
DROP Table Table;
// Traitement des enregistrements sans séparateur
Table:
NoConcatenate
LOAD START_DATE,
END_DATE,
ID_WEEK
Resident TableTemp
Where NB_SEPARATEUR = 0;
FOR a=1 to $(vMax)
Table:
Concatenate
LOAD START_DATE,
END_DATE,
Mid(ID_WEEK,1,Index(ID_WEEK,';')-1) as ID_WEEK
Resident TableTemp
Where NB_SEPARATEUR > 0
and $(a)=1;
Table:
Concatenate
LOAD START_DATE,
END_DATE,
Mid(ID_WEEK,Index(ID_WEEK,';',($(a)-1))+1,(Index(ID_WEEK,';',$(a)-1)-1)) as ID_WEEK
Resident TableTemp
Where NB_SEPARATEUR > 0
and $(a)>1
and NB_SEPARATEUR > $(a);
Table:
Concatenate
LOAD START_DATE,
END_DATE,
PurgeChar(Mid(ID_WEEK,Index(ID_WEEK,';',$(a)-1)+1),';') as ID_WEEK
Resident TableTemp
Where NB_SEPARATEUR = $(a);
NEXT
TableFinale:
NoConcatenate
LOAD *
Resident Table
where ID_WEEK <> '';
DROP Table TableTemp;
DROP Table Table;
.