Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
@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.
Hi @sidhiq91
unfortunately, it didn't work
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
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;
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