Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rustyfishbones
Master II
Master II

Transform an unstructured file?

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#SQLWidthMaxWidthERRORNAMETable
02:006017masterfileauditedfiles
03:006017unixfileauditedfiles
04:00105appl-idauditedfiles
06:006025object-nameauditedfiles
08:006022image-file-nameauditedfiles
09:00105set-image-appl-idauditedfiles
11:006021set-image-object-nameauditedfiles
02:00122warehouseaudittrxdeleted
03:00122trx-typeaudittrxdeleted
06:00488customeraudittrxdeleted
07:00Date0trx-dateaudittrxdeleted
10:0036044commentaudittrxdeleted
11:0011415added-byaudittrxdeleted
12:00Date0added-onaudittrxdeleted
13:00160edited-byaudittrxdeleted
14:00Date0edited-onaudittrxdeleted

Any ideas

1 Solution

Accepted Solutions
Anonymous
Not applicable

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 ;

View solution in original post

6 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anonymous
Not applicable

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 ;

rustyfishbones
Master II
Master II
Author

Thanks Steve and Bill

Bill's method worked better for me and I also learned something new.

Regards

Alan

rustyfishbones
Master II
Master II
Author

Thanks Steve.

Surely a Qlik Luminary for 2015!

All the best

rustyfishbones
Master II
Master II
Author

Actually I am still missing the Tablename

But I will try figure it out based on what I have thanks

Regards

Alan

Anonymous
Not applicable

Alan

This bit should help with the Tablename

  if(len(Table)=0,Peek('Table'),Table) as Table



Bill