topic How to remove records per ID in QlikView

How to remove records per ID

PresidReporting — Sat, 16 Nov 2024 18:37:26 GMT

Hi,

I am trying to remove certain records from my result set based on a certain criteria, but due to how the data is it is becoming difficult.

Example:

Opportunity#	ManufacturerID
2558	-1
2558	588
230	-1

In the example above, I would like to remove the record with opportunity# 2558 and ManufacturerID of -1. Simply telling Qlikview to remove any ManufacturerID with -1 won't work because there are Opportunities that have only one record (with a manufacturerID of -1) and we want to keep those.

Basically, any Opportunity that returns more than one record of ManufacturerIDs I would like to remove the -1 records, however if there is only one record for the Opportunity keep that one record.

In addition, this needs to be done via the loadscript because I am storing the data into a .txt file for SQL to grab.

Thoughts?

Re: How to remove records per ID

Saravanan_Desingh — Mon, 18 May 2020 19:28:12 GMT

Solution in the Script:

tab1: LOAD * INLINE [ Opportunity#, ManufacturerID 2558, -1 2558, 588 230, -1 231, 213 231, 211 ]; Left Join(tab1) LOAD Opportunity#, Count(Opportunity#) As Cnt Resident tab1 Group By Opportunity# ; tab2: NoConcatenate LOAD * Resident tab1 Where Cnt=1 OR (Cnt>1 And ManufacturerID<>-1) ; Drop Table tab1;

Re: How to remove records per ID

Saravanan_Desingh — Mon, 18 May 2020 19:29:02 GMT

Re: How to remove records per ID

Vegar — Tue, 19 May 2020 04:46:09 GMT

This might help you.

Load *,

Opportunity# as _opp

From Data

Where ManufacturerID <> -1;

Concatenate load *

From Data

Where

Not Exists(Opportunity# , _opp)

And ManufacturerID =-1 ; //not necessary

Drop field _opp;