Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone.
I would like to delete certain values in a specific pattern. Please refer to the attached picture file.
I want to delete no values from column, delete last value in column 1, delete last 2 values in column 2, delete last 3 values in column... and so on.
Can this be done through qlikview or do i have to do it manually in excel? Thanks
No worries, Kashif, it can be done in Qlikview by loading the data in a loop.
If I get you right, the script should work with something like this:
yourtablename:
load
accidentdays,
column1,
rowno() as rownumber
from
[yourfilename.xls, etc (need to use the document load assistant here)];
let vRowMax = peek(rownumber, -1);
let vRow=1;
for $(vRow) to $(vRowMax)
left join (yourtablename)
load
accidentdays,
columname$(vRow)
resident yourtablename;
let vRow = $(vRow) +1;
next
I am sure that some of our gurus here would do it in a much more elegant way, but this is how I would adress it.
You can then either re-export your data from a table report in QV or store it via the script
store * from tablename into tablename.csv (txt);
This command comes very handy when having to export hundreds of csv-files.
There is an other way around.
i assume in date column you have the latest date in last cell so what you have to do is first load the table get max date in a variable named 'max_date' then drop the table..
again load the table and for each column you can write following conditions
if(date=max_date,0,column1) as column1
if(date=date(num(max_date)-1),0,column2) as column2
if(date=date(num(max_date)-2),0,column3) as column3
and so on.....
hope this might help.
..oops, correct:
yourtablename:
load
accidentdays,
column1,
rowno() as rownumber
from
[yourfilename.xls, etc (need to use the document load assistant here)];
let vRowMax = peek(rownumber, -1);
let vRow= $(vRowMax);
let vColumn= 1;
let vColMax= [enter number of columns]
for 1 to $(vColMax)
left join (yourtablename)
first $(vRow)
load
accidentdays,
columname$(vColumn)
resident yourtablename;
let vColumn = $(vColumn)+1;
let vRow = $(vRow) -1;
next