Replace blank values with text in UI qliksense

[Aspiring_Developer]

Hello All,

Please help me with the below scenario:-

I have this expression in my pivot table:-

only({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>} COMMENT_TEXT)

Now inside this expression , I want to replace null values in field Comment text with some text.

I have the expression on how to do it  -=Aggr( if( len(trim(COMMENT_TEXT))=0, 'No Input bu user', COMMENT_TEXT), COMMENT_TEXT )

But can i combine this inside the existing expression mentioned above.

I cannot do this in backend, i have to achieve this in front end.

Thanks is advance.

@sunny_talwar  

[Aspiring_Developer]

Hello All,

I tried to do it in the backend also , still no result :-

[jyothish8807 · Accepted solution]

May be try this:

only({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>} if( len(trim(COMMENT_TEXT))=0, 'No Input bu user', COMMENT_TEXT))

You need to confirm if the missing value is actually a blank value or missing in your data set. If it is missing then you might have to create a dummy data.

[Aspiring_Developer]

Hello @jyothish8807 

It is missing in my data set 

[jyothish8807]

Hi @Aspiring_Developer ,

In that case try creating a dummy value for the same 🙂

May be like this , for eg you have two tables:

Master:

Id_master, Sale

1,  10

2, 30

3, 50

Comment:

id_comment, Comment

1, ABC

2, CDE

concatenate (Comment)

load distinct

id_comment,

'No Value' as Comment

resident Master where not exists (id_comment,id_master);

[Aspiring_Developer]

Hello @jyothish8807 

Thanks for your post, i am able to populate the string value for blank records.

However, now i have different issue. I have below table :-

As you can see I have two records for test uat2 having different comments , one is 'testtt' which is added by user and other one is 'No input by user' which is added by me to replace blank value.

When i take the same thing in pivot table, i am unable to see the second record  of test uat:-

I don't know why it is working in one visual and not in other visualization of pivot. I have not suppressed null value for any dimension. I have checked the box 'Include null values ' for all columns.

The expression for comment is here below :-

only({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>} if( len(trim(COMMENT_TEXT))=0, 'No Input bu user', COMMENT_TEXT))

And for Traffic Light:-

=pick(match(only(activity_color),'error','none','success','warning'), RGB(203,67,53), RGB(247,249,249), RGB(39,174,96), RGB(243,156,18))

Can anyone please help ? 

@sunny_talwar  

[jyothish8807]

May be try:

Aggr(only({<INPUT_DATE = {">=$(=Date(MonthStart(Max([INPUT_DATE]))))<=$(=Date(Max([INPUT_DATE])))"}>} if( len(trim(COMMENT_TEXT))=0, 'No Input bu user', COMMENT_TEXT)),[Product Name],if( len(trim(COMMENT_TEXT))=0, 'No Input bu user', COMMENT_TEXT)))