Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ziabobaz
Contributor II

Renaming values in a field in script

Hi!

I have a resulting Maintable with the following fields:

Channel

Source

Visits

I want to run a final cycle where i want to replace certain text information inside the Source field

Noconcatenate //replacing the original Maintable with new one

Maintable:

Load

Channel, Visits, // ho i have to repeat the names of all existing fields here? My actual field list is huge (if i need all of them, where can i get them as a list of values?

if (Source contains "facebook", 'facebook',

     if (Source contains "instagram", 'instagram', Source) ) as Source // This is what I am trying to achieve, what is the correct syntax?

Resident Maintable;

Thank you!

1 Solution

Accepted Solutions
ziabobaz
Contributor II

Re: Renaming values in a field in script

I ended up with a separate mapping table. It worked.


SourceMappingTable: 

NoConcatenate 

Load


source,

if(wildmatch(source,'*facebook*')=1,'facebook',

if(wildmatch(source,'*stagram*')=1,'instagram',source)) as source_clean


RESIDENT Main_table;

4 Replies
micheledenardi
Valued Contributor

Re: Renaming values in a field in script

Hi,

you don't need to repeat all fields during resident load.

The best way to achieve what you want is to create a mapping table with the source string and the new string:

ReplaceTextTable:

Mapping

Load

    SourceString,

    TargetString

Inline [

SourceString, TargetString

facebook, Facebook

face book, Facebook

instagram, Instragram

];

Rename field Source as Source_TMP;

Rename Table Maintable to Maintable_TMP;

Maintable:

NoConcatenate

Load *,

    Applymap('ReplaceTextTable',Source_TMP,Source_TMP) as Source

Resident Maintable_TMP;

Drop Table Maintable_TMP;

Drop field Source_TMP;

christophebraul
Contributor III

Re: Renaming values in a field in script

Hi,

Using preceding load is a good pratice to achieve this kind of things. You can do someting like :

Maintable:

LOAD *,

          If(SubStringCount(lower(Source_old),'facebook')=1,'facebook',

               If(SubStringCount(lower(Source_old),'instagram')=1,'instagram',Source_old)) as Source

;

LOAD Channel,

          Source as Source_old,

          Visits,

          other fields ...

FROM YourSourceFile ;

DROP FIELD Source_old;

Edit : You can also use wildmatch(Source_old,'*facebook*')=1 instead of the SubStringCount function

ziabobaz
Contributor II

Re: Renaming values in a field in script

  1. facebook, Facebook 
  2. face book, Facebook 
  3. instagram, Instragram 

Thank you!


What if I don't know all possible values of 'facebook' beforehand? It can be written with numerous typos.



ziabobaz
Contributor II

Re: Renaming values in a field in script

I ended up with a separate mapping table. It worked.


SourceMappingTable: 

NoConcatenate 

Load


source,

if(wildmatch(source,'*facebook*')=1,'facebook',

if(wildmatch(source,'*stagram*')=1,'instagram',source)) as source_clean


RESIDENT Main_table;

Community Browser