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: 
elinamcastelletta
Partner - Contributor II
Partner - Contributor II

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
Taoufiq_Zarra

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

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
Taoufiq_Zarra

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

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
elinamcastelletta
Partner - Contributor II
Partner - Contributor II
Author

Thank you. It is working.

 

What if I only need to split by rows?

Taoufiq_Zarra

What if I only need to split by rows?

Can you develop a little bit more

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
elinamcastelletta
Partner - Contributor II
Partner - Contributor II
Author

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

 

Thanks!