topic Re: Transform field characters. in QlikView

Transform field characters.

Judicael — Thu, 26 Dec 2019 16:39:06 GMT

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.

Re: Transform field characters.

Kushal_Chawda — Thu, 26 Dec 2019 19:43:21 GMT

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;

Re: Transform field characters.

Taoufiq_Zarra — Thu, 26 Dec 2019 20:59:27 GMT

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)

Re: Transform field characters.

Taoufiq_Zarra — Thu, 26 Dec 2019 21:01:08 GMT

data source

Re: Transform field characters.

Judicael — Fri, 27 Dec 2019 15:45:28 GMT

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.

Re: Transform field characters.

Judicael — Fri, 27 Dec 2019 15:46:02 GMT

Merci!

Re: Transform field characters.

Kushal_Chawda — Fri, 27 Dec 2019 20:30:12 GMT

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