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: 
ravindraa
Creator
Creator

Pick the perticular word

Hi all,

I have struck one place, my requierment is we have two field  like below.

  Company name,        sales

    Samsung_Galaxys5,    1

    Samsung galaxys5,    4

    sonyexperiaxz,       4

    Samsung_Galaxys6,    6

    Samsung_Galaxys7,    7

here we need to display the value like when company name have '_' next value need othere wise need to display the all values.

like

Galaxys5,

Galaxys6,

Galaxys7,

sonyexperiaxz,

Samsung galaxys5

to get this i have wrote below expression in caliculated Dimension like below.

subfield([company name],'_',2) in this case i am getting only like below.

Galaxys5,

Galaxys6,

Galaxys7,

i am losing below values

sonyexperiaxz,

Samsung galaxys5

please guide me any one how to do this. thanks.

1 Solution

Accepted Solutions
sujeetsingh
Master III
Master III

Here is a script for you

Dqata:

LOAD * INLINE [

   Company name,        sales

    Samsung_Galaxys5,    1

    Samsung galaxys5,    4

    sonyexperiaxz,       4

    Samsung_Galaxys6,    6

    Samsung_Galaxys7,    7

];

ETL:

LOAD *,

SubField([Company name],'_',2) as new

,SubField([Company name],' ',2) as new2

Resident Dqata;

DROP Table Dqata;

Data:

LOAD

[Company name],

sales,

if(len(new)=0 and len(new2)=0,[Company name], if(len(new)=0,new2,new)) as Model

Resident ETL;

View solution in original post

6 Replies
sujeetsingh
Master III
Master III

Try using wildmatch() function

tresesco
MVP
MVP

Try like:

If (Index([company name], '_'), subfield([company name],'_',2) , [company name]) as NewField

ravindraa
Creator
Creator
Author

Thank Tresesco,

Now it is working fine, thank you very much....

sujeetsingh
Master III
Master III

Here is a script for you

Dqata:

LOAD * INLINE [

   Company name,        sales

    Samsung_Galaxys5,    1

    Samsung galaxys5,    4

    sonyexperiaxz,       4

    Samsung_Galaxys6,    6

    Samsung_Galaxys7,    7

];

ETL:

LOAD *,

SubField([Company name],'_',2) as new

,SubField([Company name],' ',2) as new2

Resident Dqata;

DROP Table Dqata;

Data:

LOAD

[Company name],

sales,

if(len(new)=0 and len(new2)=0,[Company name], if(len(new)=0,new2,new)) as Model

Resident ETL;

ravindraa
Creator
Creator
Author

Thanks Sujeetsingh,

  it is also working fine ,thanks you very much.

sujeetsingh
Master III
Master III

You are always welcome man