Remove the record

[moath]

Hi,

I need to remove the entire record if it has at least one Null value, for example:

Here I have a Null Price for Service A in Jan. How can I remove all records of Service A once it has at least one Null Price?

The output should be like this (Only B):

YEAR	Month_PL	Service	Price
2022	Jan	B	150
2022	Feb	B	160

Is there a way to do it in QlikView script?

[PrashantSangle · Accepted solution]

In backend create flag for it. then using where not exist you can remove it from your data set

for example

BaseData:
Load *,
if(IsNull(Price) or len(trim(Price))=0,'NullService','ValidService') as serviceFlag
Inline [
Year,Month,Service,Price
2022,Jan,A,
2022,Jan,B,150
2022,Feb,A,100
2022,Feb,B,160
];

NoConcatenate
NullService:
Load Service as NullService Resident BaseData where serviceFlag = 'NullService';

NoConcatenate
FinalData:
Load Year,
Month,
Service,
Price
Resident BaseData
Where not Exists(NullService,Service);

Drop table BaseData, NullService;

Regards,

Prashant Sangle

[moath]

Hi @PrashantSangle 

Thanks for your reply.

I wrote the following:

Preparation:

LOAD Service,
Price,
month(Date) as Month,
year(Date) as Year

FROM
[C:\Users\A\Desktop\A.xlsx]
(ooxml, embedded labels, table is Sheet1);

store Preparation into [C:\Users\A\Desktop\Preparation.qvd](qvd);

BaseData:

Load *,
if(IsNull(Price) or len(trim(Price))=0,'NullService','ValidService') as serviceFlag

FROM
[C:\Users\A\Desktop\Preparation.qvd](qvd);

NoConcatenate
NullService:
Load Service as NullService Resident BaseData where serviceFlag = 'NullService';

NoConcatenate
FinalData:
Load Year,
Month,
Service,
Price
Resident BaseData
Where not Exists(NullService,Service);

Drop table BaseData, NullService;

The result is:

Service A should be removed but it's not. can you help me here?

Thank you in advance!

[PrashantSangle]

Can you share your excel?

what is value for Price? where Month=Jan, Service=A & Year=2022

Regards,

[moath]

Service	Date	Price
A	01-01-22
A	01-02-22	100
B	01-01-22	150
B	01-02-22	160

[PrashantSangle]

Looking at your data, logic which I suggest must work.

[moath]

your logic is working, but the problem when I load the data from excel instead of using Inline clause it will not work.

You can check the script that I adjusted above, you might find the solution.

thank you in advance!

[moath]

Thanks @PrashantSangle 

It worked! 

I added drop table to the table Preparation

[sidhiq91]

I am a bit late, You can follow the below logic as well.

NoConcatenate
Temp:
Load * inline [
Year, Month, Service, Price
2022, Jan, A
2022, Jan, B,150
2022, Feb, A,100
2022, Feb, B, 160
];

Inner join (Temp)
Load Service
Resident Temp
where len(trim(Price))=0;

NoConcatenate
Temp1:
Load * Inline [
Year1, Month1, Service1, Price1
2022, Jan, A
2022, Jan, B,150
2022, Feb, A,100
2022, Feb, B, 160
]
Where not exists(Service, Service1);

Drop table Temp;

Exit Script;

[moath]

Hi @PrashantSangle 

I applied the following script to a dummy data, and it was working properly, then I applied the same script to the real data and it didn't work! 

can you help me on that please?

PL_6:

LOAD Distinct Key,
PRICE,
SEGMENT_CODE,
SERVICE_CODE,
DY,
DM,
DY_DM

FROM
[D:\QV\Transformed Data\PL_5$(Report_period).qvd]
(qvd)

where DY_DM='2021_1' or DY_DM='2021_2' ;

store PL_6 into [D:\QV\Transformed Data\PL_6$(Report_period).qvd];

drop table PL_6;

BaseData:

Load *,
if(IsNull(PRICE) and len(trim(PRICE))=0 ,'NullService','ValidService') as serviceFlag

FROM
[D:\QV\Transformed Data\PL_6$(Report_period).qvd](qvd);

NoConcatenate
NullService:
Load SERVICE_CODE as NullService Resident BaseData where serviceFlag = 'NullService';

NoConcatenate
FinalData:
Load
SEGMENT_CODE,
SERVICE_CODE,
DY,
DM,
PRICE,
DY_DM

Resident BaseData

Where not Exists(NullService,SERVICE_CODE);

store FinalData into [D:\QV\Transformed Data\FinalData$(Report_period).qvd](qvd);

Drop table BaseData, NullService;