LOAD STATEMENT / Resident Load

[tahirkhalil]

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

[settu_periasamy]

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;

[tahirkhalil]

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?

[settu_periasamy]

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;