Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Replace empty field by the word others

Hello All,

Could you please help me in replacing the empty space with the word others.

I tried the mapping but it is not working:

mapservicestatus2:

mapping load * inline [

  raison_refus_rendez-vous,Newraison_refus_rendez-vous

  Null(), Autres

  ] ;

AS_XX_XXX:

ApplyMap('mapservicestatus2',[raison_refus_rendez-vous]) as [Newraison_refus_rendez-vous];

And this is my expression:

=Count({<Marquenew={'Citroën'}>}{<[Newraison_refus_rendez-vous]={'Rendez-vous déjà fixé','Véhicule vendu','Entretien déjà effectué','Autres'}>} [Newraison_refus_rendez-vous])

Any idea where I am wrong?

Many Thanks,

Hasvine

11 Replies

Re: Replace empty field by the word others

Hi,

In script try like

if(Isnull(FileName) or len(trim(fieldName))=0,'Others',FieldName)

Regards

Regards,
Prashant Sangle

Re: Replace empty field by the word others

Your set expression seems wrong.

Should be,

=Count({<Marquenew={'Citroën'},[Newraison_refus_rendez-vous]={'Rendez-vous déjà fixé','Véhicule vendu','Entretien déjà effectué','Autres'}>} [Newraison_refus_rendez-vous])

sasikanth
Valued Contributor III

Re: Replace empty field by the word others

hi,

try something like

=Count({<Marquenew={'Citroën'}>}{<[Newraison_refus_rendez-vous]={'Rendez-vous déjà fixé','Véhicule vendu','Entretien déjà effectué','Autres'}>} if( isnull([Newraison_refus_rendez-vous]),'Others',[Newraison_refus_rendez-vous]))

MVP
MVP

Re: Replace empty field by the word others

If I understand you correctly, you need this script to map nulls and spaces to 'Autres':

nullclean:

LOAD null(), 'Autres' Autogenerate 1;

LOAD '', 'Autres' Autogenerate 1;

mapservicestatus2:

Mapping LOAD * Resident nullclean;

DROP table nullclean;

 

AS_XX_XXX:

ApplyMap('mapservicestatus2', [raison_refus_rendez-vous], 'Autres') as [Newraison_refus_rendez-vous];

Now your expression:

=Count({<Marquenew={'Citroën'}, [Newraison_refus_rendez-vous]={'Rendez-vous déjà fixé','Véhicule vendu','Entretien déjà effectué', 'Autres'}>} [Newraison_refus_rendez-vous])

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Replace empty field by the word others

HI,

Try like this, no need of Mapping Load & Applymap(). just use If with Len() & Trim() like below

TableName:

LOAD

*,

If(Len(Trim([raison_refus_rendez-vous])) = 0 , 'Autres', [raison_refus_rendez-vous]) as [Newraison_refus_rendez-vous]

FROM DataSource;


Hope this helps you.


Regards,

Jagan.

Not applicable

Re: Replace empty field by the word others

Hi All,

Thanks for your quick response.

My graph shows something this:

Capture4.PNG

I tried the tips provided by and then I get the pink bar but it is not link to the 'Autres'.

I usually do the mapping and it works fine when I replace a word by another but the blank cell doesn't work.

The null shows like that in the database:

Capture5.PNG

Thanks,

Hasvine

Re: Replace empty field by the word others

Hi,

If possible share sample app.

Regards

Regards,
Prashant Sangle
MVP
MVP

Re: Replace empty field by the word others

Are those nulls in your screenshot, or are they text values '{null}'?

Either my script above, or Jagan's script (which is better if all you need to convert is the nulls), will convert the null values into 'Autres' and fix your chart.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Replace empty field by the word others

Hi Jonathan,

They are null . I tried both ways and the I get the same result as shown in the screen shot. It creates a new bar in pink (the 417 and it marks it as '-')

:-(

Thanks,

Hasvine

Community Browser