topic Re: Text file Transformation Error in QlikView

Text file Transformation Error

regowins — Mon, 10 Feb 2014 15:52:06 GMT

Hi,

I am trying to source a text file using the fixed records file type and then do a conditional delete transformation on the rows I do not want. The Transformed script looks like the below: However, when I realod the document I get an error (attached) message saying 'Field not found - <@1:8>' Not sure what is causing this . If I dont do any transformation the table loads fine with the fixed records.

LOAD @1:8 as ID,
     @9:14 as Gross,
     @15:43 as Client_Name,
     @44:55 as City,
     @56:64 as State,
     @65:77 as Invoice,
     @78:88 as Change,
     @89:98 as Amount,
     @99:112 as Number,
     @113:n as Date
FROM
Example.TXT
(fix, codepage is 1252, header is 8 lines, filters(
Remove(Row, RowCnd(CellValue, 5, StrCnd(null))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'TO'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'RE'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, '--'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'A/'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'AC'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'WI'))),
Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'CH'))),
Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'XX'))),
Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'A/R')))
));

Thanks for the help!

Re: Text file Transformation Error

Gysbert_Wassenaar — Mon, 10 Feb 2014 16:00:59 GMT

Try putting square brackets around the field names. For example [@1:8] as Gross. Alternatively you could try using a where clause. Can you post a sample txt file?

Re: Text file Transformation Error

regowins — Mon, 10 Feb 2014 16:07:12 GMT

So I tried using the square brackets and it still did not work. I have attached the example text file.

Thanks!

Re: Text file Transformation Error

maxgro — Mon, 10 Feb 2014 16:48:11 GMT

starting from Gysbert directions

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

load RowNo() as RN,*

where

len(trim(State))>0

and not WildMatch(State, 'TO', 'RE', '--', 'A/', 'AC', 'WI', 'CH')

and not WildMatch(State, 'A/R', 'STATE')

//and not WildMatch(Change, 'XX', 'A/R')

;

//Remove(Row, RowCnd(CellValue, 5, StrCnd(null))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'TO'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'RE'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, '--'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'A/'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'AC'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'WI'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'CH'))),

//Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'XX'))),

//Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'A/R')))

LOAD

[@1:8] as ID,

[@9:14] as Gross,

[@15:43] as Client_Name,

[@44:55] as City,

[@56:64] as State,

[@65:77] as Invoice,

[@78:88] as Change,

[@89:98] as Amount,

[@99:112] as Number,

[@113:n] as Date

FROM

Example.TXT

(fix, codepage is 1252, header is 8 lines

//,

//filters(

//Remove(Row, RowCnd(CellValue, 5, StrCnd(null))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'TO'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'RE'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, '--'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'A/'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'AC'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'WI'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(contain, 'CH'))),

//Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'XX'))),

//Remove(Row, RowCnd(CellValue, 7, StrCnd(contain, 'A/R')))

//)

);

Re: Text file Transformation Error

Gysbert_Wassenaar — Mon, 10 Feb 2014 16:54:27 GMT

Try this:

LOAD @1:8 as ID,

@9:14 as Gross,

@15:43 as Client_Name,

@44:55 as City,

@56:64 as State,

@65:77 as Invoice,

@78:88 as Change,

@89:98 as Amount,

@99:112 as Number,

@113:n as Date

FROM comm106893.txt

(fix, codepage is 1252, header is 8 lines)

where @65:77 ;

As long as your invoice numbers are numbers this will work. @65:77 will be a number and therfore evaluated as TRUE, which is why where @65:77 will load only the records where Invoice is a number.

Re: Text file Transformation Error

regowins — Mon, 10 Feb 2014 17:12:10 GMT

Thanks both solutions worked! However this is part of another question I had Bring Text file into QV with Transformation Do you have any ideas on how I can accomplish that? I am able to get the dates into separate variables and next step was to do what we did here. however, I dont know how to assign a date to various areas as described in the link.

Thanks again!

Re: Text file Transformation Error

maxgro — Mon, 10 Feb 2014 17:56:53 GMT

see attachment

Re: Text file Transformation Error

regowins — Tue, 11 Feb 2014 15:26:33 GMT

This is great! Exactly what I needed. I was going down a different path but your version is much simpler.

Thanks again!!!