4 Replies Latest reply: Apr 8, 2013 8:00 AM by John Ophof RSS

    Missing data

    John Ophof

      My dashboard must show training data followed by our colleagues. This goes fine if we have employees with training data.

      If we have missing data an incomplete picture is displayed see my 2 attachments.

       

      This is the code.

       

      = if (

                (TRAINING_REGISTRATION_STATUS='Completed'

                          AND MAND_TRAINING_EXPIRES = 1

                          AND((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) < 14)

                          OR (TRAINING_REGISTRATION_STATUS='Completed'

                          AND MAND_TRAINING_EXPIRES = 0),

                          1,

                          if( TRAINING_REGISTRATION_STATUS='Completed' AND

                                                        MAND_TRAINING_EXPIRES = 1 AND

                                                        ((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) < 18 AND

                                                        ((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) >= 14,2,

       

                          if( TRAINING_REGISTRATION_STATUS='Completed' AND

                                                        MAND_TRAINING_EXPIRES = 1 AND

                                                        ((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) >= 18,3,3)

                                                        ))

       

      So how can I extend this?

       

      Thanks John

        • Re: Missing data
          Nirav Bhimani

          Hi,

           

          While loading the table in the script apply this condition

           

          where not WildMatch(ColName,' ') or len(ColName) <> 0

           

          Regards,

          Nirav Bhimani

            • Re: Missing data
              John Ophof

              Hi,

               

              I think this is the load part of training data:

               

              MAN_TRAININGS:

              LOAD Distinct

                        MAND_TRAINING_NAME,

                        MAND_TRAINING_CODE AS TRAINING_CODE,

                        MAND_TRAINING_GROUP

              RESIDENT MandatoryTraining;

               

              LEFT JOIN (MAN_TRAININGS)

               

                        LOAD

                                  EMPLOYEE_ID

                        RESIDENT Career_Learning;

               

              The left join is with a fixed list of rows holding the mandatory training names. So how to embed your suggestion?

               

              John

                • Re: Missing data
                  Nirav Bhimani

                  Hi,

                   

                  Create New Table as follow,

                   

                  NoConcatenate

                  Final:

                  load * Resident MAN_TRAININGS

                  where not WildMatch(TRAINING_CODE,' ') or len(TRAINING_CODE) <> 0;

                   

                  Drop table MAN_TRAININGS;

                   

                  One more doubt in your left join, according to your example in your main table doesnot have EMPLOYEE_ID for joining ????

                   

                  Regards,

                  Nirav Bhimani