This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
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):
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
How about something like this ?
if(len(Table)=0,Peek('Table'),Table) as Table
not wildmatch ([Fld#], '---*' )
not [Fld#]= 'Fld#'
len(Fld#) > 0
@1 as [Fld#] ,
@2 as [SQLWidth] ,
@3 as [MaxWidth] ,
@4 as [ERRORNAME] ,
@5 as [Table]
(txt, codepage is 1252, no labels, delimiter is spaces, msq)
not [Fld#]= 'Current'
drop table Temp ;
View solution in original post
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.
Thanks Steve and Bill
Bill's method worked better for me and I also learned something new.
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
This bit should help with the Tablename