Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
moath
Contributor III
Contributor III

Remove the record

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?

moath_0-1655195770570.png

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?

Labels (4)
1 Solution

Accepted Solutions
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

9 Replies
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
moath
Contributor III
Contributor III
Author

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:

moath_0-1655201639612.png

 

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,

Great dreamer's dreams never fulfilled, they are always transcended.
moath
Contributor III
Contributor III
Author

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.

 

Great dreamer's dreams never fulfilled, they are always transcended.
moath
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

Thanks @PrashantSangle 

It worked! 

I added drop table to the table Preparation

sidhiq91
Specialist
Specialist

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
Contributor III
Contributor III
Author

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;