Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for 
Search instead for 
Did you mean: 
johnca
Specialist
Specialist

Transformation of Data

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

1 Solution

Accepted Solutions
rwunderlich

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

View solution in original post

5 Replies
johnca
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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

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
Specialist
Specialist
Author

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.