Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI,
I have a scenario to define a new filed in second load statement based on first load.
I have Compliance Standard based on Activity Type (STYPE) as follows:
[ComplianceStandard]:
LOAD * INLINE [
STYPE,ComplianceStandard
CS,2
GB,5
BGS,5
BKS,5
MGS,4
MKS,4
GT,3
];
The ActualData contain detailed data - with three KPI - each KPI need to be checked with Compliance Standard and if all 3 KPIs >= ComplianceStandard - the SName will be called compliant.
[ActualData]:
LOAD MONTH, YEAR, SName, STYPE, KPI1_Yes, KPI2_Yes, KPI3_Yes
FROM
[..\ActualData.qvd]
(qvd);
How should I tweak these statement to incorporate check
=if(KPI1_Yes>=ComplianceStandard and KPI2_Yes>=ComplianceStandard and KPI3_Yes>=ComplianceStandard, "Compliant", "Non-Compliant") AS Compliant_Status
Regards,
Tahir
Hi Tahir,
Try with Mapping Load and ApplyMap..
[ComplianceStandard]:
Mapping
LOAD * INLINE [
STYPE,ComplianceStandard
CS,2
GB,5
BGS,5
BKS,5
MGS,4
MKS,4
GT,3
];
Then
LOAD if(KPI1_Yes>= ApplyMap(MappingTableName,'STYPE') and KPI2_Yes >= ApplyMap(MappingTableName,'STYPE' and
KPI3_Yes>=ApplyMap(MappingTableName,'STYPE'),'Compliant', 'Non-Compliant') as Compliant_Status
From
QVD file;
Hi Settu,
Thank you -
Can you suggest one more point - if I got STYPE is in link with another table like:
[SType_Link]:
LOAD Stype, SCode FROM someQVD (qvd);
AND ActualData contain this SCode like:
[ActualData]:
LOAD MONTH, YEAR, SName, Scode, KPI1_Yes, KPI2_Yes, KPI3_Yes
FROM
[..\ActualData.qvd]
(qvd);
How we will use double applymap here?
Hi,
i think double applymap is not a problem here. But, i think, you can first Map the STYPE data. Then you can use my first suggestion. Still you want to do double apply map, May be try like the below
[SType_Link]:
Mapping LOAD Stype, SCode FROM someQVD (qvd);
[ComplianceStandard]:
Mapping
LOAD * INLINE [
STYPE,ComplianceStandard
CS,2
GB,5
BGS,5
BKS,5
MGS,4
MKS,4
GT,3
];
LOAD if(KPI1_Yes>= ApplyMap('STypeLink', ApplyMap('ComplianceStandard', STYPE), 'No Value') and
KPI2_Yes>= ApplyMap('STypeLink', ApplyMap('ComplianceStandard', STYPE), 'No Value') and
KPI3_Yes>=ApplyMap('STypeLink', ApplyMap('ComplianceStandard', STYPE), 'No Value'),
'Compliant', 'Non-Compliant') as Compliant_Status
From
QVD file;