Text to column in Qlikview

[Anonymous]

Hi

So far all i have asked has been really easy for you guys so this time i hope i have a challenge 🙂

I import data from an excel file from a scada system to get an overview on system performance and down time, as i get more data i see more possibilities to refine the filters and presentation of root cause.

In the second column in the above clip i have an extract from the description column of my excel file, there are 2 main types of data style

1. C01: text

2. C01:text (POsition coordinates)

I can make a text to column adjustment in excel before the upload and create 3 columns 1 for the data before the : the next for the text and a third for position coordinates, however i want to minimise the manual data manipulation before upload.

The reason is that i would like to be able to see the frequency of a certain error type regardless of code or position coordinate but still be able to show the frequency of errors /coordinate.

Hope this makes sense,

Thanks

Des

[marcus_sommer]

You could use subfield() to split your string, for example:

subfield(Text, ': ', 1) as Code,

subfield(subfield(Text, ': ', 2), ' (', 1) as Description,

'(' & subfield(subfield(Text, ': ', 2), ' (', 2) as Position

- Marcus

[vishsaggi · Accepted solution]

Are you looking for this? Then load your excel file and use below:

Table1:

LOAD Type,

     [Entry Time],

     [End Time],

     Duration,

     Text,

     Subfield(Text, ':',1) AS Text1,

     Subfield(Subfield(Text, ':', 2), '(',1) AS Text2,

     TextBetween(Text, '(', ')') AS Positions,

     [Additional Info 1],

     [Group 1],

     [Group 2],

     [Group 3],

     [Group 4]

FROM

[KPI 2017 - Qlik example.xlsm]

(ooxml, embedded labels, table is Export);

[Anonymous]

Thanks

Both answers worked, guess i will have to try harder to find something challenging

All the best

Des