Transform field characters.

[Judicael]

Hello,

I have 2 fields ID, DESCRIPTION

ID =FR12

DESCRIPTION=Diner : 180.00Déjeuner : 200.00Collation : 30.00Transport A/R (sur justificatifs) : 1000.00Hébergement avec petit déjeuner : 750.00

How can I have a table with this:

ID, LABEL, VALUE

FR12, Diner, 180.00

FR12, Déjeuner , 200.00

FR12, Collation, 30.00

FR12, Transport A/R (sur justificatifs), 1000.00

FR12, Hébergement avec petit déjeuner, 750.00

Thanks for your help.

[Kushal_Chawda · Accepted solution]

T1:
LOAD RowNo() as Rec,
ID,
DESCRIPTION,
SubField(DESCRIPTION,':') as DESCRIPTION_NEW
FROM [lib://Download/Transform_Fields.xls]
(biff, embedded labels, table is Sheet1$);

T2:
Load *,
if(left(trim(PurgeChar(SubField(ConcatValues,'-',1),
'0123456789')),1)='.',trim(PurgeChar(SubField(ConcatValues,'-',1),
'0123456789.')),trim(PurgeChar(SubField(ConcatValues,'-',1),
'0123456789'))) as LABEL,
trim(KeepChar(SubField(ConcatValues,'-',2),
'0123456789.')) as VALUE1;
Load *,
if(ID =Previous(ID) ,Previous(DESCRIPTION_NEW)&'-'&DESCRIPTION_NEW,DESCRIPTION_NEW) as ConcatValues
Resident T1
where len(trim(DESCRIPTION_NEW))>0
Order by ID,Rec
;

Drop Table T1;

Drop Fields Rec,DESCRIPTION_NEW,ConcatValues;

T3:
NoConcatenate
Load *
Resident T2
where len(trim(VALUE1))>0;

Drop Table T2;

Value:
Load Distinct RowNo() as RecNo,
ID,
VALUE1,
SubField(VALUE1,'.')as VALUE2
Resident T3;

Value2:
Load *,
if(ID=Previous(ID) and VALUE1=Previous(VALUE1),num(Previous(VALUE2))&'.'&VALUE2) as VALUE
Resident Value
where len(trim(VALUE2))>0
Order by ID,VALUE1,RecNo;

Drop Table Value;

Left Join(T3)
Load Distinct ID,
VALUE1,
VALUE
Resident Value2
where len(Trim(VALUE))>0;

Drop Table Value2;

Drop Field VALUE1;

[Taoufiq_Zarra]

I propose you the following scenario

only it is necessary to have a mapping table which contains the whole of what you propose for example :

Déjeuner
Collation
Hébergement avec petit déjeune
Transport A/R (sur justificatifs)
Diner
Honoraires/Stand
Transfert / Pass Metro
Inscription
Pause
Café d'accueil

the idea is to delimit the text.

Input:
LOAD ID, 
     DESCRIPTION
FROM
.\Transform_Fields.xls
(biff, embedded labels, table is Sheet1$);

Mapping1:

Mapping load * inline [
DESCRIPTION,valeur
Déjeuner,_Déjeuner
Collation,_Collation
Hébergement avec petit déjeune,_Hébergement avec petit déjeune
Transport A/R (sur justificatifs),_Transport A/R (sur justificatifs)
Diner,_Diner
Honoraires/Stand,_Honoraires/Stand
Transfert / Pass Metro,_Transfert / Pass Metro
Inscription,_Inscription
Pause,_Pause
Café d'accueil,_Café d'accueil
];

output:
noconcatenate

load MapSubString('Mapping1', DESCRIPTION) as Description_map,
* resident Input;

drop table Input;


output1:
noconcatenate
load *,MapSubString('Mapping1', subfield(Description_map,'_')) as Label_1 resident output;
drop table output;

output2:
noconcatenate
load *,TextBetween(Label_1,'_',':') as Label,num(Keepchar(Label_1, '0123456789.')) as VALUE resident output1 where len(trim(Label_1))>0;
drop table output1;

attached the Qlikview file and the results obtained

(mais entre nous 🙂 c'est un beau challenge, pour la partie mapping je pense que c'est jouable puisque la liste des services je pense que ça sera fixe et connue à l'avance)

[Taoufiq_Zarra]

data source

[Judicael]

Hello,

Thanks fot reply. I have a duplicate line 

Café d'accueil : 10.00Déjeuner : 50.00Diner : 60.00Hébergement : 163.00Hébergement avec petit déjeuner : 177.00Pause : 10.00Transport A/R (sur justificatifs) : 565.00

Café d'accueil and Pause are duplicate.

Thanks for your help.

[Judicael]

Merci!

[Kushal_Chawda]

Did the solution provided worked for you? If so please mark the reply as correct