topic Re: remove record that have same value in QlikView

remove record that have same value

Anonymous — Sat, 24 Jun 2017 06:18:53 GMT

Hi all,

I have a table like this

Item	Name	Cost	Record Date	Price
A001	Apple	10	01/01/2017	15
A001	Apple	10	02/01/2017	15
A001	Apple	11	03/01/2017	15
A002	Orange	8	01/01/2017	12
A002	Orange	10	02/01/2017	12
A003	Mango	15	01/01/2017	18
A003	Mango	15	02/01/2017	18
A003	Mango	15	03/01/2017	20

I want to remove those records where Cost and Price is same as previous record (Line 2 and 7 in the table), do you have any idea on how to do that?

Thanks!

Re: remove record that have same value

antoniotiman — Sat, 24 Jun 2017 06:29:13 GMT

Hi Paddy,

Temp:
LOAD Item,
Name,
Cost,
[Record Date],
Price
FROM
"https://community.qlik.com/message/1294316"
(html, codepage is 1252, embedded labels, table is @1);
NoConcatenate LOAD Item,Name,Cost,Price,FirstValue([Record Date]) as [Record Date]
Resident Temp
Group By Item,Name,Cost,Price;
Drop Table Temp;

Regards,

Antonio

Re: remove record that have same value

MK_QSL — Sat, 24 Jun 2017 09:41:14 GMT

Load Item, Name, Cost, Date(Min([Record Date])) as [Record Date], Price Group By Item, Name, Cost, Price;
LOAD Item, 
     Name, 
     Cost, 
     [Record Date], 
     Price
FROM
[https://community.qlik.com/thread/265343]
(html, codepage is 1252, embedded labels, table is @1);

Re: remove record that have same value

effinty2112 — Sat, 24 Jun 2017 10:05:04 GMT

Hi Paddy,

Without changing the script create this table with dims: Item,Name,Cost,Price and Record Date.

and the expression

if(Above(TOTAL Only(Cost))=Only(Cost) and Above(TOTAL Only(Price))=Only(Price),Null(),'Show')

Item	Name	Record Date	Cost	Price
A001	Apple	01/01/2017	10	15	Show
A001	Apple	03/01/2017	11	15	Show
A002	Orange	01/01/2017	8	12	Show
A002	Orange	02/01/2017	10	12	Show
A003	Mango	01/01/2017	15	18	Show
A003	Mango	03/01/2017	15	20	Show

In the presentation tab hide the expression column to get:

Item	Name	Record Date	Cost	Price
A001	Apple	01/01/2017	10	15
A001	Apple	03/01/2017	11	15
A002	Orange	01/01/2017	8	12
A002	Orange	02/01/2017	10	12
A003	Mango	01/01/2017	15	18
A003	Mango	03/01/2017	15	20

Cheers

Andrew

Re: remove record that have same value

sunny_talwar — Sat, 24 Jun 2017 11:51:12 GMT

Another option if your data is correctly sorted

Table:

LOAD AutoNumber(Item&Cost&Price) as Key,

Item,

Name,

Cost,

[Record Date],

Price

FROM

[https://community.qlik.com/thread/265343]

(html, codepage is 1252, embedded labels, table is @1)

Where not Exists(Item) or not Exists(Cost) or not Exists(Price);

Re: remove record that have same value

MK_QSL — Sat, 24 Jun 2017 12:45:11 GMT

By this way we are getting one line less.

A002

Orange

02/01/2017

Re: remove record that have same value

sunny_talwar — Sat, 24 Jun 2017 13:30:21 GMT

That makes sense... this will fix the problem

Table:

LOAD *

Where not Exists(Key);

LOAD AutoNumber(Item&Cost&Price) as Key,

Item,

Name,

Cost,

[Record Date],

Price

FROM

[https://community.qlik.com/thread/265343]

(html, codepage is 1252, embedded labels, table is @1);

Re: remove record that have same value

Kushal_Chawda — Sat, 24 Jun 2017 15:20:03 GMT

Data:

LOAD Item,

Name,

Cost,

Cost&Price as Cost_Price,

[Record Date],

Price

FROM

[https://community.qlik.com/thread/265343]

(html, codepage is 1252, embedded labels, table is @1);

New:

LOAD *

where Flag=0;

LOAD *,

if(Cost_Price=Previous(Cost_Price),1,0) as Flag

Resident Data

Order by Item,Name, [Record Date];

DROP Table Data;

DROP Fields Cost_Price,Flag;