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: 
dnowebdn
Contributor
Contributor

tesst

Hi,

I would like to have a solution on the follwing scenario

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

Here is a solution actually:

2018-08-10 20_34_46-_Multiple Lines in Excel Cell into Multiple Rows in Qlik Sense - My new sheet _ .png

T:

LOAD

    Module,

    Team AS Team_,

    Subject AS Subject_

FROM [LIB://DATA/Multiple Lines in Excel Cell into Multiple Rows in Qlik Sense.xlsx]

(ooxml, embedded labels, table is Multiline);


T_Lines:

LOAD

  Module,

  If( Peek('Module')=Module, Peek('R#')+1, 1) AS R#,

  SubField(Team_,Chr(10)) AS Team

RESIDENT

  T;


OUTER JOIN


LOAD

  Module,

  If( Peek('Module')=Module, Peek('R#')+1, 1) AS R#,

  SubField(Subject_,Chr(10)) AS Subject

RESIDENT

  T;


T_LINES:

NOCONCATENATE LOAD

  Module,

  R#,

  If( IsNull(Team) , '' , Team) AS Team,

  If( IsNull(Subject) , '' , Subject) AS Subject

RESIDENT

  T_Lines

ORDER BY

  Module,R#;


DROP TABLE T_Lines;

DROP FIELDS Team_,Subject_;

View solution in original post

12 Replies
dnowebdn
Contributor
Contributor
Author

any suggestions please

bwisealiahmad
Partner - Specialist
Partner - Specialist

Hi,

I've seen people use Chr(10) for this before, but I couldn't seem to make it work...

Would love to see a solution for this.

I was thinking Replacing (a)'s with Chr(10) might work

Forslag petter-s‌?

Best,

Ali A

petter
Partner - Champion III
Partner - Champion III

As far as I know there is absolutely no way to make the lines break while still having them in a single row in a Qlik table (internal table or UI table). You will have to do something in the load script most probably to break one value consisting of several text lines into rows/records in a detail table connected to the original table also maintaining the order of the lines ... Quite a hack - but it is not too complicated.

You might be able to implement it in expressions in the UI too... but it might be even more of a hack than doing it in the load script and less flexible I guess.

dnowebdn
Contributor
Contributor
Author

I have write logic however,  I am sharing but it yields duplicate and not giving proper results.

Can you suggest me

rubenmarin

Hi you can try with:

replace(FieldName, '@', chr(13)) as FieldName,

Note that tables won't adjust to the heigh of data, heigh of rows is fixed, so if you set height to 3, it will shows 3 lines height for all records.

dnowebdn
Contributor
Contributor
Author

Hi Ruben,

It will remove the symbol but my point is to break the rows after the symbol.

So, can you suggest any such logic please

rubenmarin

replace(FieldName, '@', '@'& chr(13)) as FieldName,

dnowebdn
Contributor
Contributor
Author

can you send me if the expression is working as expected please.

dnowebdn
Contributor
Contributor
Author

any response please