topic Re: how to split the values in excel with more functions in QlikView

how to split the values in excel with more functions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,Experts.

My extracted table data having more functions in one excel cell.I want split the values into different rows. Iam attached a table.

In my data one of the column is Group.The Group column is one of the sheet(i,e.LH Driver Safety Scorecard) of attached data.please give me a suggestion.

Thanks in advance.

Re: how to split the values in excel with more functions

Quy_Nguyen — Fri, 29 Jun 2018 06:31:37 GMT

Try this function:

https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/StringFunctions/SubField.htm

Example:

Load *, SubField(Group,'___') as NewField;

LOAD * INLINE

[

Group

NYALA___TC-993684___PC-ZM10000, NYALA EMAIL

]

Re: how to split the values in excel with more functions

Anonymous — Fri, 29 Jun 2018 08:29:10 GMT

Hi,Quy Nguyen.Thank you giving good suggestion.

IAm using same expression. Its possible only one row value to splitting purpose.When i execute the data is combining with ',' & '__'.So i want split hole data of group column and the data spit & the split data should be separate row.So please give another suggestion.

Thanks.

Re: how to split the values in excel with more functions

Quy_Nguyen — Fri, 29 Jun 2018 08:52:01 GMT

Hi,

Just replace the ',' by '___'

Load *, RowNo(), SubField(Replace(Group,',','___'),'___') as NewField;

LOAD * INLINE

[

Group

'NYALA___TC-993684___PC-ZM10000, NYALA EMAIL'

'SATWANT___TC-944083___PC-ZM10000, SATWANT EMAIL'

'STALLION MOTORS___TC-943658___PC-ZM10010'

'TOOL OF TRADE'

];

Re: how to split the values in excel with more functions

Anonymous — Fri, 29 Jun 2018 13:00:58 GMT

Its working.

Thanks for give good suggestion.

Re: how to split the values in excel with more functions

Quy_Nguyen — Fri, 29 Jun 2018 13:42:21 GMT

Glad it helped. If your question has been answered with your satisfaction, please mark the appropriate reply as correct/helpful.