Transformation of Data

[johnca]

I need some help transforming the attached data into something useful. This is an example of data I receive once per week and I want to be able to trend each process (i.e. RABBIT200) and node (i.e. JELLY) and PID (i.e. 206BA1BF) by week.

Anyone have some free time?

V/r,

John

[johnca]

BTW, I am trying to get this export in .csv format. Not sure what it will look like.

[rwunderlich]

That would be a key point, whether it is csv in a different layout or if you have to deal with this report format. 

-Rob

[johnca]

I figured as much, Rob, but I do appreciate the confirmation. Have requested this export...

[rwunderlich · Accepted solution]

If you have to use the txt report format, here's a start. 

SET Verbatim=1;
Raw:
LOAD
if([@1:n] like 'Quotas for*', alt(peek('Key')+1, 1), peek('Key')) as Key
,if([@1:n] like 'Quotas for*', 'header', 'detail') as RecType
,[@1:n] as RawField
FROM
[Quota Report 30-JAN-2019 - Sample.txt]
(fix, codepage is 28591, no labels)
where left([@1:n],1) <> ' '
and len([@1:n]) > 0
;

Process:
LOAD
Key
,TextBetween(RawField, 'process', ',') as Process
,TextBetween(RawField, 'user ', ' ') as User
,TimeStamp#(TextBetween(RawField, 'at ', ','),'DD-MMM-YYYY hh:mm') as DateTime
,TextBetween(RawField, 'node ', ' ') as Node
,Text(TextBetween(RawField, 'PID ', ' ')) as PID
Resident Raw
where RecType = 'header'
;

Metrics:
LOAD
Key
,trim(mid(RawField,1,20)) as Metric
,trim(mid(RawField,22,8)) as Quota
,trim(mid(RawField,33,8)) as Free
,trim(mid(RawField,47,4)) as Used
,trim(mid(RawField,54)) as Status
Resident Raw
where RecType = 'detail'
;

DROP TABLE Raw;

If you want to transpose the metrics to columns, generic load the Metrics table. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[johnca]

Just AWESOME, Rob! I can certainly work with this! 

I was told the .csv export was some 7 worksheets and a macro to format it all into this nice little email text breaks when they try to do it the way I asked.