Remove record

[moath]

Hi

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! 

The purpose here is to remove the whole record once I have "0" or null values.

can anyone 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;

[sidhiq91]

@moath I was just wondering why to use exists Function, once you have created the  serviceflag using the QVD File, just a where clause would solve issue right? Something like below:

NoConcatenate
FinalData:
Load
SEGMENT_CODE,
SERVICE_CODE,

DY,
DM,
PRICE,
DY_DM,

ServiceFlag

Resident Basedata

where match(Serviceflag,'ValidService');

If this resolves your issue kindly like and accept it as a solution.

[moath]

Hi @sidhiq91 

unfortunately, it didn't work

[marcus_sommer]

Your flag-creation-condition will probably not be working like you expect because you applies two conditions with an AND linking:

if(IsNull(PRICE) and len(trim(PRICE))=0 ,'NullService','ValidService')

which means that both conditions must be true to get a TRUE as result. I suggest to change it to:

if(len(trim(PRICE))=0 ,'NullService','ValidService')

which would fetch real NULL but also missing/empty values or spaces.

Beside this you should create the flag already at the first possible opportunity - means already within:

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

or even earlier if there are more ETL steps in beforehand or during this qvd-load.

- Marcus

[moath]

Hi @marcus_sommer 

can you help me where should I add the logic to the following script?

PL_5:

LOAD SEGMENT_CODE & '-' & SERVICE_CODE & '-' & DY & '-' & DM as Key,
PRICE,
SEGMENT_CODE,
SERVICE_CODE,
TOP_PROVIDERS,
DY,
DM,
DY&'_'&DM as DY_DM,

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

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

drop table PL_5;

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

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

drop table PL_6;

BaseData:

Load *,
if(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;

[marcus_sommer]

You may add it here:

PL_5:
if(len(trim(PRICE))=0,'NullService','ValidService') as serviceFlag,
LOAD SEGMENT_CODE & '-' & SERVICE_CODE & '-' & DY & '-' & DM as Key,
PRICE,
SEGMENT_CODE,
SERVICE_CODE,
TOP_PROVIDERS,
DY,
DM,
DY&'_'&DM as DY_DM,

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

whereby it might be possible to add it already within the D:\QV\Transformed Data\PL_4$(Report_period).qvd or even earlier. It looked as if there is a rather long ETL chain and within each step are just a few transformations whereby it didn't look like as if it were different layer. I think I would tend to skip a few of these steps and doing everything more together.

- Marcus