Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Judicael
Partner - Contributor III
Partner - Contributor III

Transform field characters.

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.

1 Solution

Accepted Solutions
Kushal_Chawda

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;

View solution in original post

6 Replies
Kushal_Chawda

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

Capture.PNG

 

(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)

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Taoufiq_Zarra

data source

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Judicael
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

Merci!

Kushal_Chawda

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