topic Re: Delete selected values in QlikView

Delete selected values

kash04kk — Tue, 30 Jun 2015 06:57:50 GMT

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

Re: Delete selected values

Sat, 04 Jul 2015 09:38:35 GMT

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;

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.

Re: Delete selected values

shair_abbas — Sat, 04 Jul 2015 22:51:57 GMT

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.

Re: Delete selected values

Tue, 07 Jul 2015 06:10:15 GMT

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