Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
MVP
MVP

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
Highlighted
MVP
MVP

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

Highlighted
Master
Master

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") 😉
Highlighted
Master
Master

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") 😉
Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

Merci!

Highlighted
MVP
MVP

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