Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Aspiring_Developer
Creator III
Creator III

Replace blank values with text in UI qliksense

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)

Aspiring_Developer_0-1632995537091.png

 

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  

 

1 Solution

Accepted Solutions
jyothish8807
Master II
Master II

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.

Best Regards,
KC

View solution in original post

6 Replies
Aspiring_Developer
Creator III
Creator III
Author

Hello All,

 

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

Aspiring_Developer_0-1632997334027.png

 

Aspiring_Developer_2-1632997372689.png

 

jyothish8807
Master II
Master II

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.

Best Regards,
KC
Aspiring_Developer
Creator III
Creator III
Author

Hello @jyothish8807 

 

It is missing in my data set 

jyothish8807
Master II
Master II

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

Best Regards,
KC
Aspiring_Developer
Creator III
Creator III
Author

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 :-

Aspiring_Developer_0-1633015080717.png

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:-

Aspiring_Developer_1-1633015203892.png

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
Master II
Master II

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

Best Regards,
KC