Missing data

[#1]

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

[nirav_bhimani]

Hi,

While loading the table in the script apply this condition

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

Regards,

Nirav Bhimani

[#3]

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

[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

[#5]

So having this issue still what to do to resolve this properly.

No data all the trainings myst be mentioned.

John