topic Re: Missing data in QlikView

Missing data

Thu, 28 Mar 2013 06:35:05 GMT

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),

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 — Thu, 28 Mar 2013 07:05:41 GMT

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

Thu, 28 Mar 2013 08:10:38 GMT

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 — Thu, 28 Mar 2013 08:22:00 GMT

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

Re: Missing data

Mon, 08 Apr 2013 12:00:29 GMT

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

No data all the trainings myst be mentioned.

John