Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
Can you share sample data and expected output?
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;
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
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)))
));
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 .
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.
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:
Thanks Vineeth, This will work.
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 ;