Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a file that contains hundreds of data extracts from tables similar to below
Current max fieldLen for auditedfiles (1):
Fld# SQLWidth Max Width ERROR NAME
---- -------- --------- ----- ----
2: 60 17 masterfile
3: 60 17 unixfile
4: 10 5 appl-id
6: 60 25 object-name
8: 60 22 image-file-name
9: 10 5 set-image-appl-id
11: 60 21 set-image-object-name
Current max fieldLen for audittrxdeleted (2):
Fld# SQLWidth Max Width ERROR NAME
---- -------- --------- ----- ----
2: 12 2 warehouse
3: 12 2 trx-type
6: 48 8 customer
7: Date 0 trx-date
10: 360 44 comment
11: 114 15 added-by
12: Date 0 added-on
13: 16 0 edited-by
14: Date 0 edited-on
What I need to do is create a clean table in Qlikview that will look like this, I have hundreds of rows, I tried cleaning it up with SUBFIELD, TEXTBETWEEN etc.... but can't get it the way I want
Fld# | SQLWidth | MaxWidth | ERRORNAME | Table |
02:00 | 60 | 17 | masterfile | auditedfiles |
03:00 | 60 | 17 | unixfile | auditedfiles |
04:00 | 10 | 5 | appl-id | auditedfiles |
06:00 | 60 | 25 | object-name | auditedfiles |
08:00 | 60 | 22 | image-file-name | auditedfiles |
09:00 | 10 | 5 | set-image-appl-id | auditedfiles |
11:00 | 60 | 21 | set-image-object-name | auditedfiles |
02:00 | 12 | 2 | warehouse | audittrxdeleted |
03:00 | 12 | 2 | trx-type | audittrxdeleted |
06:00 | 48 | 8 | customer | audittrxdeleted |
07:00 | Date | 0 | trx-date | audittrxdeleted |
10:00 | 360 | 44 | comment | audittrxdeleted |
11:00 | 114 | 15 | added-by | audittrxdeleted |
12:00 | Date | 0 | added-on | audittrxdeleted |
13:00 | 16 | 0 | edited-by | audittrxdeleted |
14:00 | Date | 0 | edited-on | audittrxdeleted |
Any ideas
How about something like this ?
Temp :
load
[Fld#] ,
[SQLWidth] ,
[MaxWidth] ,
[ERRORNAME] ,
if(len(Table)=0,Peek('Table'),Table) as Table
;
Load
*
where
not wildmatch ([Fld#], '---*' )
and
not [Fld#]= 'Fld#'
and
len(Fld#) > 0
;
LOAD
@1 as [Fld#] ,
@2 as [SQLWidth] ,
@3 as [MaxWidth] ,
@4 as [ERRORNAME] ,
@5 as [Table]
FROM
(txt, codepage is 1252, no labels, delimiter is spaces, msq)
;
Data :
NoConcatenate
load
Rowno() ,
*
resident Temp
where
not [Fld#]= 'Current'
;
drop table Temp ;
Hi Alan,
Looking at your example data there are no spaces that couldn't be used as delimiters. Is this the case for all of the data?
If so you should be able to do it in two parses.
First, load the file with no delimiters or quoting on the data. Parse this on the way in with the following, to make each multiple space into another delimiter:
replace(replace(replace(replace(replace(DataLine, ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' '), ' ', ',') as Delimited
You can then store this to a new text file. That text file you should be able to load in as delimited text.
I'm sure there is a much more elegant way of removing the multiple spaces to get them down to one space, but you get the idea.
Probably worth doing one file at a time in a for each vFile in FolderList('*.txt') rather than trying to do it with a load with a wildcard.
Hope that helps.
Steve
How about something like this ?
Temp :
load
[Fld#] ,
[SQLWidth] ,
[MaxWidth] ,
[ERRORNAME] ,
if(len(Table)=0,Peek('Table'),Table) as Table
;
Load
*
where
not wildmatch ([Fld#], '---*' )
and
not [Fld#]= 'Fld#'
and
len(Fld#) > 0
;
LOAD
@1 as [Fld#] ,
@2 as [SQLWidth] ,
@3 as [MaxWidth] ,
@4 as [ERRORNAME] ,
@5 as [Table]
FROM
(txt, codepage is 1252, no labels, delimiter is spaces, msq)
;
Data :
NoConcatenate
load
Rowno() ,
*
resident Temp
where
not [Fld#]= 'Current'
;
drop table Temp ;
Thanks Steve and Bill
Bill's method worked better for me and I also learned something new.
Regards
Alan
Thanks Steve.
Surely a Qlik Luminary for 2015!
All the best
Actually I am still missing the Tablename
But I will try figure it out based on what I have thanks
Regards
Alan
Alan
This bit should help with the Tablename
if(len(Table)=0,Peek('Table'),Table) as Table
Bill