Load Statement is not working properly

[#1]

The below Load statement is not working proper, it is wrapping most the components in just the EDX option;

LOAD *,

   IF([COMPONENT]='cpms aw application prd' OR [COMPONENT] = 'cpms aw - phoenix licence server software','CPMS',

IF(COMPONENT='tscg','TSCG',

IF([COMPONENT]='electronic document exchange(edx) access improvement tst (itst)' OR

[COMPONENT]='electronic document exchange (edx) safety prd' OR [COMPONENT]<='electronic document exchange (edx) prd' OR

[COMPONENT]='electronic document exchange (edx) scd+1 (primary) integrated test (iuat)' OR [COMPONENT] ='electronic document exchange (edx) qa' OR [COMPONENT]='confidential disclosure agreement (cda) prd','EDX',

IF([COMPONENT]='harp analysis and reporting app prd' OR [COMPONENT]='harp analysis and reporting app prd' OR [COMPONENT]='harp integration application – prd' OR COMPONENT='harp analysis and reporting app val'

OR COMPONENT='harp analysis and reporting app dev' OR [COMPONENT]='biometrics environment linux utilities (bioenv) prd' OR [COMPONENT]='sas on uk1salx00118 prd' OR [COMPONENT]='sas on uk1salx00119 prd','HARP',

IF([COMPONENT]='healthcare information factory (hif) prd gdc' OR [COMPONENT]='hif visualisations prd kop' OR [COMPONENT]='hif visualisations tst kop','HIF',

IF([COMPONENT]='clinical data visibility tool - spotfire server prd'  OR [COMPONENT]='clinical data visibility tool - workflow refresh prd','CDVT',

IF([COMPONENT]<='dmext dataset manager val','DATASET MANAGER',

IF([COMPONENT]='gsk study portal (gsp) prd' OR [COMPONENT]='gsk study portal (gsp) qa' OR [COMPONENT]='gsk study portal (gsp) tst (itst)','GSP',

IF([COMPONENT]='risk based monitoring (rbm) test' OR [COMPONENT]='risk based monitoring (rbm) test','Risk Based Monitoring',

IF([COMPONENT]='enavigator tst' OR [COMPONENT]='enavigator uat','ENAVIGATOR',

IF([COMPONENT]='inet catalist prd kop','INET CATALIST',

IF([COMPONENT]='alsc life sciences cloud - pre prod' OR [COMPONENT]='alsc life sciences cloud - prod' OR [COMPONENT]='alsc life sciences cloud - test' OR [COMPONENT]='alsc cognos reporting - test','Life Science Cloud',

IF([COMPONENT]='decisionview study optimizer stv prd','STUDY OPTIMIZER',

IF([COMPONENT]='dmext spectre prd' OR [COMPONENT]='spectre suite','SPECTRE',

IF([COMPONENT]='decisionview site optimizer prd','SITE OPTIMIZER',

IF([COMPONENT]='transfer of obligations - production','TRANSFER OF OBLIGATION',

IF([COMPONENT]='archive quick check 2.0  prd','ARCHIVE QUICK CHECK','SUPPLIER CONTRACT'))))))))))))))))) AS TICKET_APP;

please someone help me look into, any help would appreciated. stalwar1‌ omar‌

[OmarBenSalem]

Why there Component <= sthing? It makes no sense !

LOAD *,

  IF([COMPONENT]='cpms aw application prd' OR [COMPONENT] = 'cpms aw - phoenix licence server software','CPMS',

IF(COMPONENT='tscg','TSCG',

IF([COMPONENT]='electronic document exchange(edx) access improvement tst (itst)' OR

[COMPONENT]='electronic document exchange (edx) safety prd' OR [COMPONENT]<='electronic document exchange (edx) prd' OR

[COMPONENT]='electronic document exchange (edx) scd+1 (primary) integrated test (iuat)' OR [COMPONENT] ='electronic document exchange (edx) qa' OR [COMPONENT]='confidential disclosure agreement (cda) prd','EDX',

IF([COMPONENT]='harp analysis and reporting app prd' OR [COMPONENT]='harp analysis and reporting app prd' OR [COMPONENT]='harp integration application – prd' OR COMPONENT='harp analysis and reporting app val'

OR COMPONENT='harp analysis and reporting app dev' OR [COMPONENT]='biometrics environment linux utilities (bioenv) prd' OR [COMPONENT]='sas on uk1salx00118 prd' OR [COMPONENT]='sas on uk1salx00119 prd','HARP',

IF([COMPONENT]='healthcare information factory (hif) prd gdc' OR [COMPONENT]='hif visualisations prd kop' OR [COMPONENT]='hif visualisations tst kop','HIF',

IF([COMPONENT]='clinical data visibility tool - spotfire server prd'  OR [COMPONENT]='clinical data visibility tool - workflow refresh prd','CDVT',

IF([COMPONENT]<='dmext dataset manager val','DATASET MANAGER',

IF([COMPONENT]='gsk study portal (gsp) prd' OR [COMPONENT]='gsk study portal (gsp) qa' OR [COMPONENT]='gsk study portal (gsp) tst (itst)','GSP',

IF([COMPONENT]='risk based monitoring (rbm) test' OR [COMPONENT]='risk based monitoring (rbm) test','Risk Based Monitoring',

IF([COMPONENT]='enavigator tst' OR [COMPONENT]='enavigator uat','ENAVIGATOR',

IF([COMPONENT]='inet catalist prd kop','INET CATALIST',

IF([COMPONENT]='alsc life sciences cloud - pre prod' OR [COMPONENT]='alsc life sciences cloud - prod' OR [COMPONENT]='alsc life sciences cloud - test' OR [COMPONENT]='alsc cognos reporting - test','Life Science Cloud',

IF([COMPONENT]='decisionview study optimizer stv prd','STUDY OPTIMIZER',

IF([COMPONENT]='dmext spectre prd' OR [COMPONENT]='spectre suite','SPECTRE',

IF([COMPONENT]='decisionview site optimizer prd','SITE OPTIMIZER',

IF([COMPONENT]='transfer of obligations - production','TRANSFER OF OBLIGATION',

IF([COMPONENT]='archive quick check 2.0  prd','ARCHIVE QUICK CHECK','SUPPLIER CONTRACT'))))))))))))))))) AS TICKET_APP;

[#3]

yes that was a mistake, which I have rectified, but it is still same.

[danansell42]

Have you consider creating the list as either an inline load or mastered on a spreadsheet then using Applymap function?

[sunny_talwar · Accepted solution]

I suggest using ApplyMap with a mapping table for this: Data Cleansing or at least use Match and/or WildMatch here:

If(Match([COMPONENT], 'cpms aw application prd', 'cpms aw - phoenix licence server software'), 'CPMS',

If(Match(COMPONENT, 'tscg'),'TSCG',

If(Match([COMPONENT], 'electronic document exchange(edx) access improvement tst (itst)', 'electronic document exchange (edx) safety prd', 'electronic document exchange (edx) prd', 'electronic document exchange (edx) scd+1 (primary) integrated test (iuat)', 'electronic document exchange (edx) qa', 'confidential disclosure agreement (cda) prd'), 'EDX',

If(Match([COMPONENT], 'harp analysis and reporting app prd', 'harp analysis and reporting app prd', 'harp integration application – prd', 'harp analysis and reporting app val', 'harp analysis and reporting app dev' OR [COMPONENT]='biometrics environment linux utilities (bioenv) prd', 'sas on uk1salx00118 prd', 'sas on uk1salx00119 prd'), 'HARP',

If(Match([COMPONENT], 'healthcare information factory (hif) prd gdc', 'hif visualisations prd kop', 'hif visualisations tst kop'), 'HIF',

If(Match([COMPONENT], 'clinical data visibility tool - spotfire server prd', 'clinical data visibility tool - workflow refresh prd'), 'CDVT',

If(Match([COMPONENT], 'dmext dataset manager val'), 'DATASET MANAGER',

If(Match([COMPONENT], 'gsk study portal (gsp) prd', 'gsk study portal (gsp) qa', 'gsk study portal (gsp) tst (itst)'), 'GSP',

If(Match([COMPONENT], 'risk based monitoring (rbm) test', 'risk based monitoring (rbm) test'), 'Risk Based Monitoring',

If(Match([COMPONENT], 'enavigator tst', 'enavigator uat'), 'ENAVIGATOR',

If(Match([COMPONENT], 'inet catalist prd kop'), 'INET CATALIST',

If(Match([COMPONENT], 'alsc life sciences cloud - pre prod', 'alsc life sciences cloud - prod', 'alsc life sciences cloud - test', 'alsc cognos reporting - test'),'Life Science Cloud',

If(Match([COMPONENT], 'decisionview study optimizer stv prd'), 'STUDY OPTIMIZER',

If(Match([COMPONENT], 'dmext spectre prd', 'spectre suite'), 'SPECTRE',

If(Match([COMPONENT], 'decisionview site optimizer prd'), 'SITE OPTIMIZER',

If(Match([COMPONENT], 'transfer of obligations - production'), 'TRANSFER OF OBLIGATION',

If(Match([COMPONENT], 'archive quick check 2.0  prd'), 'ARCHIVE QUICK CHECK', 'SUPPLIER CONTRACT'))))))))))))))))) AS TICKET_APP;

[#6]

Thanks this works!

[#7]

Hi Daniel,

   How does this works?? , because I would be working with a more larger file and the IF statement wouldn't be the most efficient way to this working with about 300 roles of component .

[#8]

Hi Sunny,

    the below code I giving me just one output and theats 'SLA MET', do you know why?

LOAD *,

IF([TIER]='GOLD',

if(Match([EL_3_],'Yes'),'SLA MET',

    IF([TIER]='SILVER',

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=10,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=20,'SLA MET','SLA Not Met'))))))) as SLA;

[#9]

Hi Omar,

the below code I giving me just one output 'SLA MET', do you know why?

LOAD *,

IF([TIER]='GOLD',

if(Match([EL_3_],'Yes'),'SLA MET',

    IF([TIER]='SILVER',

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=10,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=20,'SLA MET','SLA Not Met'))))))) as SLA;

[OmarBenSalem]

Try as follow:

LOAD *,

IF(match([TIER],'GOLD'),

if(Match([EL_3_],'Yes'),'SLA MET',

    IF(match([TIER],'SILVER'),

IF(match([DECODE_PRIORITY],'High' ) AND [Arrival to Resolve Days]<=3,'SLA MET',

IF(match([DECODE_PRIORITY],'Urgent') AND [Arrival to Resolve Days]<=3,'SLA MET',

IF(match([DECODE_PRIORITY],'Medium') AND [Arrival to Resolve Days]<=10,'SLA MET',

IF(match([DECODE_PRIORITY],'Low') AND [Arrival to Resolve Days]<=20,'SLA MET','SLA Not Met'))))))) as SLA;