15 Replies Latest reply: Apr 13, 2017 8:36 AM by victor onyebuchi RSS

    Load Statement is not working properly

    victor onyebuchi

      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

        • Re: Load Statement is not working properly
          omar bensalem

          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;

          • Re: Load Statement is not working properly
            Sunny Talwar

            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;

              • Re: Load Statement is not working properly
                victor onyebuchi

                Thanks this works!

                • Re: Load Statement is not working properly
                  victor onyebuchi

                  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;

                    • Re: Load Statement is not working properly
                      omar bensalem

                      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;

                      • Re: Load Statement is not working properly
                        Sunny Talwar

                        May be this

                         

                        LOAD *,

                        IF([TIER]='GOLD',

                            IF(Match([EL_3_],'Yes'),'SLA MET', 'SLA Not 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;

                          • Re: Load Statement is not working properly
                            Sunny Talwar

                            Or this

                             

                            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;