Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kash04kk
New Contributor II

Delete selected values

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? Thankstest.jpg

Tags (1)
3 Replies
Not applicable

Re: Delete selected values

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.

shair_abbas
New Contributor III

Re: Delete selected values

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.

Not applicable

Re: Delete selected values

..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

Community Browser