Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

Find text and split into rows and columns

Hello,

 

I need your support to solve this issue in Qlik Sense:

I have 2 tables:

Inline Table

HOME

CAR

CHAIR

 

Data

  

Section

ID

Details

1

1a

HOMExxxxxxxxxxxCARxxxxCHAIRxxxxx

1

1b

CHAIRxxxxHOMExxCARx

1

1c

CHAIRxxxx

 

I need this output:

Section

ID

Name

Details

1

1a

HOME

XXXXXXXXXXXX

1

1a

CAR

XXXX

1

1a

CHAIR

XXXXX

1

1B

CHAIR

XXXX

1

1B

HOME

XX

1

1B

CAR

X

1

1C

CHAIR

XXXX

 

Thanks for your help,

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Find text and split into rows and columns

Maye be 🙂

 

map1:
mapping load *,'_'&CCode&',' as Value;
LOAD * 
Inline [
CCode
HOME
CAR
CHAIR
];

Data:
Load Section,ID,subfield(SubNewdetail,',',1) as Name,subfield(SubNewdetail,',',2) as Details where len(SubNewdetail)>0;
load Section,ID,subfield(Newdetail,'_') as SubNewdetail ;
LOAD *,MapSubString('map1', Details) As Newdetail INLINE [
    Section, ID, Details
    1, 1a, HOMExxxxxxxxxxxCARxxxxCHAIRxxxxx
    1, 1b, CHAIRxxxxHOMExxCARx
    1, 1c, CHAIRxxxx
];

 

Capture.PNG

 

Regards,
Taoufiq ZARRA

View solution in original post

4 Replies
Highlighted
Specialist II
Specialist II

Re: Find text and split into rows and columns

Maye be 🙂

 

map1:
mapping load *,'_'&CCode&',' as Value;
LOAD * 
Inline [
CCode
HOME
CAR
CHAIR
];

Data:
Load Section,ID,subfield(SubNewdetail,',',1) as Name,subfield(SubNewdetail,',',2) as Details where len(SubNewdetail)>0;
load Section,ID,subfield(Newdetail,'_') as SubNewdetail ;
LOAD *,MapSubString('map1', Details) As Newdetail INLINE [
    Section, ID, Details
    1, 1a, HOMExxxxxxxxxxxCARxxxxCHAIRxxxxx
    1, 1b, CHAIRxxxxHOMExxCARx
    1, 1c, CHAIRxxxx
];

 

Capture.PNG

 

Regards,
Taoufiq ZARRA

View solution in original post

Highlighted

Re: Find text and split into rows and columns

Thank you. It is working.

 

What if I only need to split by rows?

Highlighted
Specialist II
Specialist II

Re: Find text and split into rows and columns

What if I only need to split by rows?

Can you develop a little bit more

Regards,
Taoufiq ZARRA
Highlighted

Re: Find text and split into rows and columns

Oh, it is ok, I just realized i can adapt the code you sent.

 

Thanks!