Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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
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);
Thanks
Both answers worked, guess i will have to try harder to find something challenging
All the best
Des