how to trim the last x lines of a text flat file?

[Anonymous]

I am new to Qlik, but trying to load flat files.  The files have a trailing section which is always the same height.  Is there any way to transform the data to do this programatically ?

[shraddha_g]

Can you share sample data and expected output?

[maxgro]

maybe

load the table and add a recno

X:

load

  recno() as id,

  rand() as field1,

  rand()*100 as field2

AutoGenerate 100;

discard the last 10 rows

Right Keep (X)

load *

Resident X

Where id <= NoOfRows('X') -10;

[Anonymous]

Hi

following is for deleting last 2 rows of Excel or csv file

Let vNoOfColumns = NoOfFields('your table name');

ANS:

for i = 1 to nooffields('YourCSV')   // no of columns in excel file

if $(i) >= nooffields('YourCSV') - 2 then

let vFieldname = fieldname($(i), 'YourCSV');

drop field $(vFieldname);

end if

next

[vinieme12]

You can use the file transformation wizard

Below is an example showing how to remove the last three rows from a table load

Steps:

Enable Transformation Step(Appears after initial table load screen) >> Conditional Delete

This will add this particular line to your From statement

FROM

(biff, embedded labels, table is Sheet1$, filters(

Remove(Row, RowCnd(Interval, Pos(Bottom, 1), Pos(Bottom, 3), Select(1, 0)))

));

[Anonymous]

wow, thanks for all the great advice. I will research this and try it out. The trailing text is

.

PSC

filename=attente

records=0000000000002

ldbname=futur

timestamp=2016/09/29-21:05:09

numformat=44,46

dateformat=dmy-1950

map=NO-MAP

cpstream=ISO8859-1

.

0000000098

This is data file information at the end, and I would avoid removing it.  I tried Merrant ODBC but I can't seem to configure the drivers to connect to the progress 4GL database .

[maxgro]

This is for Qlikview, don't think it exists in Sense and the question is in a Sense forum.

I think you can install Sense and Qlikview on the same pc, use the Qlikview transformation wizard to create the script and then copy to Sense script.

[Anonymous]

I downloaded QlikView, figured out how to get to the transformation wizard.  I tried the transformation on a clients data file, the script looks like this:

Only problem is that it says it fetched 8119 rows, yet the real # of records is 8107.  So is the Filter clause working?

In the app if I drop down the Client# column in a sheet, the data does not seem to have truncated the trailing part:

[lakshmikandh]

Thanks Vineeth, This will work.

[vinieme12]

I don't think you will need to use the filter clause here, just use the where clause with data validation to filter only data rows.

WHERE  ISNUM(CLIENT#) = -1 ;